This command from a plain-text SQL backup (via PGAdmin) of the 2017 budget database seems to have restored all its nonsense to the default postgres database on our 2018 PostgreSQL server instead of creating a budget database and restoring there. Then when I ran the command a second time it finally dawned on me (when it reported "already exists" for a budget database I couldn't find) that it went the wrong way:
$ gzip -dc budget.sql.gz | sudo -u postgres psql
could not change directory to "/home/ec2-user": Permission denied
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
ERROR: relation "budget_app_budgethistory" already exists
ALTER TABLE
ERROR: relation "HistorySummaryByBureau" already exists
ALTER TABLE
ERROR: relation "HistorySummaryByBureauFund" already exists
ALTER TABLE
ERROR: relation "HistorySummaryByServiceArea" already exists
ALTER TABLE
ERROR: relation "auth_group" already exists
ALTER TABLE
ERROR: relation "auth_group_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "auth_group_permissions" already exists
ALTER TABLE
ERROR: relation "auth_group_permissions_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "auth_permission" already exists
ALTER TABLE
ERROR: relation "auth_permission_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "auth_user" already exists
ALTER TABLE
ERROR: relation "auth_user_groups" already exists
ALTER TABLE
ERROR: relation "auth_user_groups_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "auth_user_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "auth_user_user_permissions" already exists
ALTER TABLE
ERROR: relation "auth_user_user_permissions_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "budget_app_budgethistory_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "budget_app_kpm" already exists
ALTER TABLE
ERROR: relation "budget_app_kpm_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "budget_app_lookupcode" already exists
ALTER TABLE
ERROR: relation "budget_app_lookupcode_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "budget_app_ocrb" already exists
ALTER TABLE
ERROR: relation "budget_app_ocrb_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "django_admin_log" already exists
ALTER TABLE
ERROR: relation "django_admin_log_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "django_content_type" already exists
ALTER TABLE
ERROR: relation "django_content_type_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "django_migrations" already exists
ALTER TABLE
ERROR: relation "django_migrations_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR: relation "django_session" already exists
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 0
COPY 0
ERROR: duplicate key value violates unique constraint "auth_permission_content_type_id_01ab375a_uniq"
DETAIL: Key (content_type_id, codename)=(1, add_logentry) already exists.
CONTEXT: COPY auth_permission, line 1
COPY 0
COPY 0
COPY 0
ERROR: duplicate key value violates unique constraint "budget_app_budgethistory_pkey"
DETAIL: Key (id)=(84341) already exists.
CONTEXT: COPY budget_app_budgethistory, line 1
ERROR: duplicate key value violates unique constraint "budget_app_kpm_pkey"
DETAIL: Key (id)=(156) already exists.
CONTEXT: COPY budget_app_kpm, line 1
ERROR: duplicate key value violates unique constraint "budget_app_lookupcode_pkey"
DETAIL: Key (id)=(5258) already exists.
CONTEXT: COPY budget_app_lookupcode, line 1
ERROR: duplicate key value violates unique constraint "budget_app_ocrb_pkey"
DETAIL: Key (id)=(13732) already exists.
CONTEXT: COPY budget_app_ocrb, line 1
COPY 0
ERROR: duplicate key value violates unique constraint "django_content_type_app_label_76bd3d3b_uniq"
DETAIL: Key (app_label, model)=(admin, logentry) already exists.
CONTEXT: COPY django_content_type, line 1
ERROR: duplicate key value violates unique constraint "django_migrations_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY django_migrations, line 1
COPY 0
setval
--------
1
(1 row)
setval
--------
1
(1 row)
setval
--------
75
(1 row)
setval
--------
1
(1 row)
setval
--------
1
(1 row)
setval
--------
1
(1 row)
setval
--------
168680
(1 row)
setval
--------
347
(1 row)
setval
--------
6896
(1 row)
setval
--------
13995
(1 row)
setval
--------
1
(1 row)
setval
--------
25
(1 row)
setval
--------
25
(1 row)
ERROR: relation "auth_group_name_key" already exists
ERROR: relation "auth_group_permissions_group_id_0cd325b0_uniq" already exists
ERROR: multiple primary keys for table "auth_group_permissions" are not allowed
ERROR: multiple primary keys for table "auth_group" are not allowed
ERROR: relation "auth_permission_content_type_id_01ab375a_uniq" already exists
ERROR: multiple primary keys for table "auth_permission" are not allowed
ERROR: multiple primary keys for table "auth_user_groups" are not allowed
ERROR: relation "auth_user_groups_user_id_94350c0c_uniq" already exists
ERROR: multiple primary keys for table "auth_user" are not allowed
ERROR: multiple primary keys for table "auth_user_user_permissions" are not allowed
ERROR: relation "auth_user_user_permissions_user_id_14a6b632_uniq" already exists
ERROR: relation "auth_user_username_key" already exists
ERROR: multiple primary keys for table "budget_app_budgethistory" are not allowed
ERROR: multiple primary keys for table "budget_app_kpm" are not allowed
ERROR: multiple primary keys for table "budget_app_lookupcode" are not allowed
ERROR: multiple primary keys for table "budget_app_ocrb" are not allowed
ERROR: multiple primary keys for table "django_admin_log" are not allowed
ERROR: relation "django_content_type_app_label_76bd3d3b_uniq" already exists
ERROR: multiple primary keys for table "django_content_type" are not allowed
ERROR: multiple primary keys for table "django_migrations" are not allowed
ERROR: multiple primary keys for table "django_session" are not allowed
ERROR: relation "auth_group_name_a6ea08ec_like" already exists
ERROR: relation "auth_group_permissions_0e939a4f" already exists
ERROR: relation "auth_group_permissions_8373b171" already exists
ERROR: relation "auth_permission_417f1b1c" already exists
ERROR: relation "auth_user_groups_0e939a4f" already exists
ERROR: relation "auth_user_groups_e8701ad4" already exists
ERROR: relation "auth_user_user_permissions_8373b171" already exists
ERROR: relation "auth_user_user_permissions_e8701ad4" already exists
ERROR: relation "auth_user_username_6821ab7c_like" already exists
ERROR: relation "django_admin_log_417f1b1c" already exists
ERROR: relation "django_admin_log_e8701ad4" already exists
ERROR: relation "django_session_de54fa62" already exists
ERROR: relation "django_session_session_key_c0390e0f_like" already exists
ERROR: constraint "auth_group_permiss_permission_id_84c5c92e_fk_auth_permission_id" for relation "auth_group_permissions" already exists
ERROR: constraint "auth_group_permissions_group_id_b120cbf9_fk_auth_group_id" for relation "auth_group_permissions" already exists
ERROR: constraint "auth_permiss_content_type_id_2f476e4b_fk_django_content_type_id" for relation "auth_permission" already exists
ERROR: constraint "auth_user_groups_group_id_97559544_fk_auth_group_id" for relation "auth_user_groups" already exists
ERROR: constraint "auth_user_groups_user_id_6a12ed8b_fk_auth_user_id" for relation "auth_user_groups" already exists
ERROR: constraint "auth_user_user_per_permission_id_1fbb5f2c_fk_auth_permission_id" for relation "auth_user_user_permissions" already exists
ERROR: constraint "auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id" for relation "auth_user_user_permissions" already exists
ERROR: constraint "django_admin_content_type_id_c4bce8eb_fk_django_content_type_id" for relation "django_admin_log" already exists
ERROR: constraint "django_admin_log_user_id_c564eba6_fk_auth_user_id" for relation "django_admin_log" already exists
REVOKE
REVOKE
GRANT
GRANT
This command from a plain-text SQL backup (via PGAdmin) of the 2017 budget database seems to have restored all its nonsense to the default
postgresdatabase on our 2018 PostgreSQL server instead of creating abudgetdatabase and restoring there. Then when I ran the command a second time it finally dawned on me (when it reported "already exists" for abudgetdatabase I couldn't find) that it went the wrong way: