在Django项目中，我在Question app中有一个模型lucy_web，但是不存在对应的lucy_web_question表，如从数据库外壳中的\dt命令看到的：
(lucy-web-CVxkrCFK) bash-3.2$ python manage.py dbshell
psql (10.4)
Type "help" for help.
lucy=> \dt
List of relations
Schema | Name | Type | Owner
--------+------------------------------+-------+---------
public | auditlog_logentry | table | lucyapp
public | auth_group | table | lucyapp
public | auth_group_permissions | table | lucyapp
public | auth_permission | table | lucyapp
public | auth_user | table | lucyapp
public | auth_user_groups | table | lucyapp
public | auth_user_user_permissions | table | lucyapp
public | defender_accessattempt | table | lucyapp
public | django_admin_log | table | lucyapp
public | django_content_type | table | lucyapp
public | django_migrations | table | lucyapp
public | django_session | table | lucyapp
public | lucy_web_checkin | table | lucyapp
public | lucy_web_checkintype | table | lucyapp
public | lucy_web_company | table | lucyapp
public | lucy_web_expert | table | lucyapp
public | lucy_web_expertsessiontype | table | lucyapp
public | lucy_web_family | table | lucyapp
public | lucy_web_lucyguide | table | lucyapp
public | lucy_web_notification | table | lucyapp
public | lucy_web_package | table | lucyapp
public | lucy_web_packagesessiontype | table | lucyapp
public | lucy_web_preactivationfamily | table | lucyapp
public | lucy_web_profile | table | lucyapp
public | lucy_web_questionanswer | table | lucyapp
public | lucy_web_questioncategory | table | lucyapp
public | lucy_web_session | table | lucyapp
public | lucy_web_sessioncategory | table | lucyapp
public | lucy_web_sessiontype | table | lucyapp
public | lucy_web_userapn | table | lucyapp
public | oauth2_provider_accesstoken | table | lucyapp
public | oauth2_provider_application | table | lucyapp
public | oauth2_provider_grant | table | lucyapp
public | oauth2_provider_refreshtoken | table | lucyapp
public | otp_static_staticdevice | table | lucyapp
public | otp_static_statictoken | table | lucyapp
public | otp_totp_totpdevice | table | lucyapp
public | two_factor_phonedevice | table | lucyapp
(38 rows)
我们还有一个部署在aptible上的staging环境，它看起来确实有这些表。使用Aptible CLI to create a database tunnel，如果我
psql <connection_url>和dt看到lucy_web_question表：db=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------------------+-------+---------
public | auditlog_logentry | table | aptible
public | auth_group | table | aptible
public | auth_group_permissions | table | aptible
public | auth_permission | table | aptible
public | auth_user | table | aptible
public | auth_user_groups | table | aptible
public | auth_user_user_permissions | table | aptible
public | defender_accessattempt | table | aptible
public | django_admin_log | table | aptible
public | django_content_type | table | aptible
public | django_migrations | table | aptible
public | django_session | table | aptible
public | lucy_web_checkin | table | aptible
public | lucy_web_checkintype | table | aptible
public | lucy_web_company | table | aptible
public | lucy_web_expert | table | aptible
public | lucy_web_expertsessiontype | table | aptible
public | lucy_web_family | table | aptible
public | lucy_web_lucyguide | table | aptible
public | lucy_web_notification | table | aptible
public | lucy_web_package | table | aptible
public | lucy_web_packagesessiontype | table | aptible
public | lucy_web_preactivationfamily | table | aptible
public | lucy_web_profile | table | aptible
public | lucy_web_question | table | aptible
public | lucy_web_questionanswer | table | aptible
public | lucy_web_questioncategory | table | aptible
public | lucy_web_questionprompt | table | aptible
public | lucy_web_session | table | aptible
public | lucy_web_sessioncategory | table | aptible
public | lucy_web_sessiontype | table | aptible
public | lucy_web_userapn | table | aptible
public | oauth2_provider_accesstoken | table | aptible
public | oauth2_provider_application | table | aptible
public | oauth2_provider_grant | table | aptible
public | oauth2_provider_refreshtoken | table | aptible
public | otp_static_staticdevice | table | aptible
public | otp_static_statictoken | table | aptible
public | otp_totp_totpdevice | table | aptible
public | two_factor_phonedevice | table | aptible
(40 rows)
因为这些测试环境中的数据并不重要，所以我希望
pg_dumpaptible数据库，并在本地计算机上pg_restore它。我的局部
DATABASE_URL是postgres://lucyapp:<my_password>@localhost/lucy，所以首先，我用pg_dump做了一个--format=custom，并指定了一个--file，如下所示：Kurts-MacBook-Pro-2:lucy2 kurtpeek$ touch staging_db_12_July.dump
Kurts-MacBook-Pro-2:lucy2 kurtpeek$ pg_dump postgresql://aptible:<some_aptible_hash>@localhost.aptible.in:62288/db --format=custom --file=staging_db_12_July.dump
Kurts-MacBook-Pro-2:lucy2 kurtpeek$ ls -lhtr | tail -1
-rw-r--r-- 1 kurtpeek staff 1.5M Jul 12 18:09 staging_db_12_July.dump
这将生成一个1.5mb
.dump文件，我尝试使用pg_restore和--no-owner选项还原该文件（以便将所有者从--role=lucyapp更改为aptible）。然而，这导致了大量的“已经存在”的错误，其中一个如下所示：Kurts-MacBook-Pro-2:lucy2 kurtpeek$ pg_restore staging_db_12_July.dump --dbname=lucy --no-owner --role=lucyapp
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3522; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
pg_restore: [archiver (db)] Error from TOC entry 2; 3079 16392 EXTENSION hstore
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied to create extension "hstore"
HINT: Must be superuser to create this extension.
Command was: CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;
pg_restore: [archiver (db)] Error from TOC entry 3523; 0 0 COMMENT EXTENSION hstore
pg_restore: [archiver (db)] could not execute query: ERROR: extension "hstore" does not exist
Command was: COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key, value) pairs';
pg_restore: [archiver (db)] Error from TOC entry 197; 1259 16515 TABLE auditlog_logentry aptible
pg_restore: [archiver (db)] could not execute query: ERROR: relation "auditlog_logentry" already exists
Command was: CREATE TABLE public.auditlog_logentry (
id integer NOT NULL,
object_pk character varying(255) NOT NULL,
object_id bigint,
object_repr text NOT NULL,
action smallint NOT NULL,
changes text NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
actor_id integer,
content_type_id integer NOT NULL,
remote_addr inet,
additional_data jsonb,
CONSTRAINT auditlog_logentry_action_check CHECK ((action >= 0))
);
WARNING: errors ignored on restore: 294
问题是，如果我在
lucyapp中再次\dt，我仍然看不到python manage.py dbshell表。对于我的情况，我已经找到了这个解决方案，Django : Table doesn't exist，但是在我的情况下，
lucy_web_question模型被导入并在很多地方用作外键，所以我认为恢复数据库会更容易。但是，为什么不恢复Question表？ 最佳答案
问题似乎是lucyapp用户没有足够的权限创建表。我基本上必须确保\dn+命令产生以下结果：
lucy=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres +|
| | lucyapp=UC/postgres |
(1 row)
其中
lucyapp同时具有USAGE（U）和CREATE（C）权限。在https://www.postgresql.org/docs/9.0/static/sql-grant.html之后，可以通过以下命令来实现GRANT USAGE ON SCHEMA public TO lucyapp;
GRANT CREATE ON SCHEMA public TO lucyapp;
在运行这些命令之前，我还创建了
lucyappasuperuser，尽管不建议在生产中使用。关于django - pg_restore无法还原某些表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51316479/