PostgreSQL know table hierarchy

Question

Is there a way to know the parent - child relations (in terms of foreign keys) of all the tables in postgresql database? We are planning a full database migration using AWS DMS, we need specify in what order we can migrate our tables.

Answer 1

The documentation suggests to disable constraint checks:

If the target is a PostgreSQL-compatible database, then you can see foreign key violation errors during the CDC phase. To resolve this error, set the session_replication_role parameter to replica.

If you really want to specify the table order manually, use any of the queries from this question to get a list; for example:

SELECT
 tc.table_schema, 
 tc.table_name, 
 ccu.table_name AS foreign_table_name
FROM 
 information_schema.table_constraints AS tc 
 JOIN information_schema.constraint_column_usage AS ccu
 USING (constraint_name, table_schema)
WHERE tc.constraint_type = 'FOREIGN KEY';