Put some data tables from one database to another

[szabozoltan69]

Sometimes some data tables are required in a database that can be found in another one (Staging, Prod) for testing purpose.
Here are the steps to do it in an "SQL way". Maybe later we can develop another ("manage.py") way for that also.

1. Define a database to be the "host" database, where we inject the "guest" tables. It can be a local one also at first.
2. Collect the data tables from "guest" database this way, with different $t table names:

pg_dump -O -a -h 127.0.0.1 -U ifrcgo postgres -n public -t $t -f $t.sql

3. Cut off non-wanted header and concatenate files:

tail -q -n+23 *sql > ../guest.sql

4. Lets open the "host" database and prepare the needed tables:

alter table api_erpguid DISABLE TRIGGER ALL;
... (the same for all the other tables, one by one – you can create a script for that and run it via \i)
delete from api_erpguid;
... (the same for all the other tables, one by one – you can create a script for that and run it via \i)

5. Put the guest.sql to it's place in the database system (e.g. to /var/lib/postgresql/data, where docker base database keeps everything) and load the guest.sql data:

begin;
\i /var/lib/postgresql/data/guest.sql
end;

6. If some error occurs, find the root cause.
   An example:

psql:/var/lib/postgresql/data/guest.sql:397036: ERROR:  syntax error at or near "18443"
Check the top of the error block, it's verbose, like:
ERROR:  column "is_active" of relation "notifications_surgealert" does not exist

Fixes which comes from difference between the databases (!!!). Sometimes non-existent names (like "statusz") are good to ignore non-needed columns.

alter table notifications_surgealert add column is_active boolean;
alter table notifications_surgealert add column is_stood_down boolean;
alter table notifications_surgealert add column status integer;
alter table notifications_surgealert add column statusz text;

Sometimes MANUAL changes are needed in guest.sql, e.g. (status to molnix_status, molnix_status to statusz):

COPY public.notifications_surgealert (id, atype, category, operation, message, deployment_needed, is_private, created_at, event_id, message_ar, message_en, message_es, message_fr, operation_ar, operation_en, operation_es, operation_fr, closes, "end", molnix_id, opens, start, is_active, statusz,       country_id, is_stood_down, translation_module_original_language, translation_module_skip_auto_translation, molnix_status) FROM stdin;

original was:

COPY public.notifications_surgealert (id, atype, category, operation, message, deployment_needed, is_private, created_at, event_id, message_ar, message_en, message_es, message_fr, operation_ar, operation_en, operation_es, operation_fr, closes, "end", molnix_id, opens, start, is_active, molnix_status, country_id, is_stood_down, translation_module_original_language, translation_module_skip_auto_translation, status) FROM stdin;

Also happens that an interval of user ID-s are missing from the "host" database. In this case some dummy users can be added via a loop:

insert into auth_user(id,password,is_superuser,username,first_name,last_name,email,is_staff,is_active,date_joined) values (10188,'---','f','u10188','manually','created','10188@uh.hu','f','t','2024-01-01');
insert into auth_user(id,password,is_superuser,username,first_name,last_name,email,is_staff,is_active,date_joined) values (10189,'---','f','u10189','manually','created','10189@uh.hu','f','t','2024-01-01');

7. Delete table contents again, copy guest.sql in place.
8. Try the load again. If successful, enable triggers:

alter table api_erpguid ENABLE TRIGGER ALL;
... (the same for all the other tables, one by one – you can create a script for that and run it via \i)
...

9. Enjoy the result. (Or, if some triggers were not enabled, trace the root and fix it.)

[szabozoltan69]

In an Azure based PostgreSQL flexible server it is not allowed to disable triggers table-wise (permission denied), but there is an easier way:

BEGIN;

-- Set all constraints to deferred within this transaction
SET CONSTRAINTS ALL DEFERRED;

-- Perform your database operations here: delete from needed tables.
-- (Triggers or constraints won't fire until the end of the transaction)

COMMIT;

After the COMMIT any new operations you perform will have the constraints enforced immediately unless you explicitly defer them again.

Then the same sequence is needed for "\i ...guest.sql" command. It can not be performed in the same loop as the deletes.