Back ground

I have a project that uses PostgreSQL as its database. The project has been running for a long time, and the production database has accumulated a large amount of historical data.

The development database was originally synchronized with the production database. However, the existing data in the development environment is no longer needed.

Therefore, I need to clear all existing data from the development database while preserving the database schema, reset identity columns, and migrate the latest production data into the development environment.

1. Trucate Dev Business Tables

Truncate all tables in the public schema while preserving the database schema and restarting identity columns.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DO $$
DECLARE
    sql_text text;
BEGIN
    SELECT
        'TRUNCATE TABLE ' ||
        string_agg(format('%I.%I', schemaname, tablename), ', ') ||
        ' RESTART IDENTITY'
    INTO sql_text
    FROM pg_tables
    WHERE schemaname = 'public';

    IF sql_text IS NOT NULL THEN
        EXECUTE sql_text;
    END IF;
END $$;

This removes all data from the tables while keeping the table definitions intact. RESTART IDENTITY resets sequences owned by the truncated tables.

2. Backup Dev Database

Before performing any data migration or deletion, it is important to back up the development database to prevent accidental data loss.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
$env:PGPASSWORD = "your_password_here"  # Replace with your actual password

$SourceHost = "aws-1-ap-southeast-2.xxx.xxx.com" # Replace with your actual host
$SourcePort = "5432" # Replace with your actual port
$SourceDatabase = "postgres" # Replace with your actual database name
$SourceUser = "postgres.xxxx" # Replace with your actual username

$BackupFile = "D:\freelance\backup\xxxx.dump" # Replace with your desired backup file path

Write-Host "Starting PostgreSQL backup..."

pg_dump `
    -h $SourceHost `
    -p $SourcePort `
    -U $SourceUser `
    -d $SourceDatabase `
    -F c `
    --schema=public `
    --no-owner `
    --no-privileges `
    -f $BackupFile

if ($LASTEXITCODE -eq 0) {
    Write-Host "Backup successful: $BackupFile"
}
else {
    Write-Host "Backup failed."
    exit 1
}

The -F c option creates a PostgreSQL custom-format backup, which can later be restored using pg_restore.

3. Backup Data from Production Database

Export only the data from the production database.
Because the development database already contains the required schema, there is no need to export the production schema.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$env:PGPASSWORD = "your_password_here"  # Replace with your actual password

$SourceHost = "aws-1-ap-southeast-2.xxx.xxx.com" # Replace with your actual host
$SourcePort = "5432" # Replace with your actual port
$SourceDatabase = "postgres" # Replace with your actual database name
$SourceUser = "postgres.xxxx" # Replace with your actual username

$BackupFile = "D:\freelance\backup\yyy.dump" # Replace with your desired backup file path

Write-Host "Starting PostgreSQL backup..."

pg_dump `
    -h $SourceHost `
    -p $SourcePort `
    -U $SourceUser `
    -d $SourceDatabase `
    --data-only `
    --schema=public `
    -F c `
    --no-owner `
    --no-privileges `
    -f $BackupFile

if ($LASTEXITCODE -eq 0) {
    Write-Host "Backup successful: $BackupFile"
}
else {
    Write-Host "Backup failed."
    exit 1
}

4. Generate Foreign Key Constraints Script

Before removing foreign key constraints from the development database, generate a SQL script that can recreate them later.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$env:PGPASSWORD = "your_password_here"  # Replace with your actual password

$SourceHost = "aws-1-ap-southeast-2.xxx.xxx.com" # Replace with your actual host
$SourcePort = "5432" # Replace with your actual port
$SourceDatabase = "postgres" # Replace with your actual database name
$SourceUser = "postgres.xxxx" # Replace with your actual username

$BackupFile = "D:\freelance\backup\zzz-fk-constraints.sql" # Replace with your desired backup file path

Write-Host "Starting PostgreSQL backup..."

psql `
  -h $SourceHost `
  -p $SourcePort `
  -U $SourceUser `
  -d $SourceDatabase `
  -At `
  -c "SELECT format('ALTER TABLE %I.%I ADD CONSTRAINT %I %s;', n.nspname, t.relname, c.conname, pg_get_constraintdef(c.oid)) FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE c.contype = 'f' AND n.nspname = 'public';" `
  | Out-File -Encoding utf8 $BackupFile

if ($LASTEXITCODE -eq 0) {
    Write-Host "Backup successful: $BackupFile"
}
else {
    Write-Host "Backup failed."
    exit 1
}

The generated file contains statements similar to:

1
2
3
4
ALTER TABLE public.orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id)
REFERENCES public.customers(id);

5. Remove the Foreign Key Constraints from the Development Database

Foreign key constraints may cause problems during data restoration because tables may be restored in an order that temporarily violates referential integrity.
First, generate a SQL script to drop all foreign key constraints, and then execute the script.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
$env:PGPASSWORD = "your_password_here"  # Replace with your actual password

$SourceHost = "aws-1-ap-southeast-2.xxx.xxx.com" # Replace with your actual host
$SourcePort = "5432" # Replace with your actual port
$SourceDatabase = "postgres" # Replace with your actual database name
$SourceUser = "postgres.xxxx" # Replace with your actual username

$BackupFile = "D:\freelance\backup\zzz-fk-constraints-drop.sql" # Replace with your desired backup file path

Write-Host "Starting PostgreSQL drop fk script backup..."

psql `
  -h $SourceHost `
  -p $SourcePort `
  -U $SourceUser `
  -d $SourceDatabase `
  -At `
  -c "SELECT format('ALTER TABLE %I.%I DROP CONSTRAINT %I;', n.nspname, t.relname, c.conname) FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE c.contype = 'f' AND n.nspname = 'public';" `
  | Out-File -Encoding utf8 $BackupFile

psql `
  -h $SourceHost `
  -p $SourcePort `
  -U $SourceUser `
  -d $SourceDatabase `
  -f $BackupFile

if ($LASTEXITCODE -eq 0) {
    Write-Host "drop fk script run successful: $BackupFile"
}
else {
    Write-Host "drop fk script run failed."
    exit 1
}

6. Restore Data to Development Database

After clearing the development database and removing the foreign key constraints, restore the production data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$env:PGPASSWORD = "your_password_here"  # Replace with your actual password

$SourceHost = "aws-1-ap-southeast-2.xxx.xxx.com" # Replace with your actual host
$SourcePort = "5432" # Replace with your actual port
$SourceDatabase = "postgres" # Replace with your actual database name
$SourceUser = "postgres.xxxx" # Replace with your actual username

$BackupFile = "D:\freelance\backup\yyy.dump" # Replace with your desired backup file path

Write-Host "Starting PostgreSQL restore..."

pg_restore `
    -h $TargetHost `
    -p $TargetPort `
    -U $TargetUser `
    -d $TargetDatabase `
    --data-only `
    --no-owner `
    --no-privileges `
    --exit-on-error `
    --verbose `
    $BackupFile

if ($LASTEXITCODE -eq 0) {
    Write-Host "Restore successful."
}
else {
    Write-Host "Restore failed."
    exit 1
}

The --exit-on-error option ensures that the restore process stops immediately if PostgreSQL encounters an error.

7. Restore Foreign Key Constraints to Development Database

After the production data has been restored successfully, recreate the foreign key constraints.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$env:PGPASSWORD = "your_password_here"  # Replace with your actual password

$SourceHost = "aws-1-ap-southeast-2.xxx.xxx.com" # Replace with your actual host
$SourcePort = "5432" # Replace with your actual port
$SourceDatabase = "postgres" # Replace with your actual database name
$SourceUser = "postgres.xxxx" # Replace with your actual username

$BackupFile = "D:\freelance\backup\zzz-fk-constraints.sql" # Replace with your desired backup file path

Write-Host "Starting PostgreSQL foreign key constraints restore..."

psql `
  -h $SourceHost `
  -p $SourcePort `
  -U $SourceUser `
  -d $SourceDatabase `
  -f $BackupFile

if ($LASTEXITCODE -eq 0) {
    Write-Host "Foreign key constraints restore successful: $BackupFile"
}
else {
    Write-Host "Foreign key constraints restore failed."
    exit 1
}

If this step fails, it may indicate that the restored data contains invalid references, such as a foreign key value that does not have a corresponding row in the referenced table.

8. Verify Data Migration

After completing the data migration, it’s essential to verify that the data has been successfully restored in the development database. You can run queries to check the data integrity and ensure that the foreign key constraints are intact.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Check the number of rows in each table
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY relname;

-- Check foreign key constraints
SELECT
    tc.table_name,
    tc.constraint_name,
    kcu.column_name,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.constraint_schema = kcu.constraint_schema
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.constraint_schema = 'public'
ORDER BY tc.table_name;

-- Check indexes
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;