Copying a PostgreSQL Database to another Server
Copying a PostgreSQL Database to another Server:
Copying a PostgreSQL database to another server is a common task, especially when setting up backups, testing environments, or migrating data. PostgreSQL offers several tools to make this process efficient, primarily using pg_dump and pg_restore or by directly piping data over a network.
Method 1: Using pg_dump and pg_restore
The pg_dump utility creates a backup of the database in a format that pg_restore can then use to recreate the database on another server.
1. Dump the Database on the Source Server
# Run pg_dump to create a database backup file pg_dump -U username -h source_host -p source_port -F c -b -v -f backup_file.dump database_name
Explanation:
- -U username: Specifies the username.
- -h source_host: Specifies the source server hostname or IP.
- -p source_port: Specifies the source server port.
- -F c: Chooses the custom format, suitable for pg_restore.
- -b: Includes large objects in the backup.
- -v: Enables verbose output.
- -f backup_file.dump: Names the output backup file.
Copy the Backup file to the target Server:
# Transfer backup file to the target server using scp scp backup_file.dump target_user@target_host:/path/to/directory
Explanation:
- scp: Securely copies the file to the target server.
- target_user@target_host: Specifies the target server’s username and hostname or IP.
- /path/to/directory: Target directory path to store the backup file.
Restore the Database on the target Server:
# Run pg_restore to restore the database on the target server pg_restore -U username -h target_host -p target_port -d new_database_name -v /path/to/backup_file.dump
Explanation:
- -U username: Specifies the username.
- -h target_host: Specifies the target server hostname or IP.
- -p target_port: Specifies the target server port.
- -d new_database_name: The target database name to restore into.
- /path/to/backup_file.dump: The path to the transferred backup file.
Method 2: Directly Copying the Database Over a Network
If both servers are accessible, you can pipe the output from pg_dump directly to psql on the target server.
# Directly copy database from source to target server over a network pg_dump -U username -h source_host -p source_port database_name | psql -U username -h target_host -p target_port -d new_database_name
Explanation:
- The pipe (|) allows the pg_dump output to be used directly by psql on the target server.
- This method requires network connectivity between the servers and can be faster as it avoids writing to an intermediate file.
Example Code and Explanation
1. Dump the Database
# Dump the database into a custom format file
pg_dump -U admin_user -h 192.168.1.10 -p 5432 -F c -b -v -f mydb_backup.dump my_database
2. Transfer the Backup File
# Transfer the dump file to the target server
scp mydb_backup.dump [email protected]:/backups/
3. Restore the Database
# Restore the database on the target server
pg_restore -U admin_user -h 192.168.1.20 -p 5432 -d new_database -v /backups/mydb_backup.dump
Important Notes:
- Permissions: Ensure both source and target servers allow access for the specified user.
- Network Access: For direct copying over the network, open the necessary ports (e.g., 5432).
- Backup Format: The custom format -F c enables faster and more flexible restores with pg_restore.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/PostgreSQL/snippets/copy-postgresql-database-to-another-server.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics