Guepard offers a Migration Assistant to simplify database migrations, ensuring seamless transfers between database environments. Whether you're moving from a legacy system or transitioning between cloud and on-premise databases, Guepard supports streamlined database migration using native PostgreSQL tools like pg_dump and pg_restore.

Exporting a Database with pg_dump

pg_dump is a PostgreSQL utility used to create backups of databases in a portable format. It allows for logical backups, supports various formats, and can exclude specific tables or schemas if needed.

📌 Note: pg_dump does not lock the database, allowing backups to be taken while the database is in use.

Command to Export a Database

pg_dump -h <source-host> -U <username> -d <database_name> -F c -f backup.dump

• -h <source-host>: PostgreSQL server hostname.
• -U <username>: PostgreSQL user.
• -d <database_name>: Name of the database to dump.
• -F c: Exports in custom format (recommended for pg_restore).
• -f backup.dump: Specifies the output file name.

💡 Tip: If you need to migrate only schema changes without data, add --schema-only to your pg_dump command.

⚠️ Warning: Large databases can take time to dump. Use -j <num_jobs> to split the process across multiple jobs when dealing with large datasets.

Restoring a Database with pg_restore

pg_restore is used to restore a PostgreSQL database from a backup file created using pg_dump (custom format, directory, or tar).

📌 Note: pg_restore does not overwrite an existing database unless the -C option is used.

Ensure the Target Database Exists

Before restoring, create the target database if it does not exist:

createdb -h <target-host> -U <username> <database_name>

Restore the Database

pg_restore -h <target-host> -U <username> -d <database_name> -F c backup.dump

• -h <target-host>: PostgreSQL server hostname.
• -U <username>: PostgreSQL user.
• -d <database_name>: Target database for restoration.
• -F c: Specifies custom format.

Restoring into a New Database

pg_restore -C -h <target-host> -U <username> -d postgres -F c backup.dump

Here, -C allows recreating the database before restoring.

💡 Tip: If your database contains large objects (LOBs), use the --no-owner flag to avoid permission issues when restoring into a different environment.

⚠️ Warning: Running pg_restore on an active database can lead to inconsistencies. It is recommended to restore to a fresh database or perform maintenance mode during restoration.

Best Practices

Before migration, ensure both databases are running the same PostgreSQL version. Use pg_dump --schema-only to verify compatibility. Enable foreign key checks in the target database after migration. During migration, use compression (-Z 9) for large databases and consider staged migrations if downtime is a concern. After migration, run ANALYZE and VACUUM to optimize performance and check logs for missing extensions or configuration issues.

💡 Tip: Run pg_restore --list backup.dump before restoring to inspect the contents and ensure the backup includes all necessary objects.

Guepard simplifies database transitions by leveraging PostgreSQL-native tools. Whether migrating between environments, restoring backups, or automating migrations, Guepard ensures a smooth, efficient process.