In this article, we will see how to take backup and restore PostgreSQL table.
It is administrator regular activity to take backup and restore PostgreSQL table from production server to development or UAT servers.
PostgreSQL Tools for Backup and Restore table:
1. pg_dump: Extract a PostgreSQL table into a script file or other archive file.
2. psql: PostgreSQL interactive terminal used to load backup taken using pg_dump.
3. scp is used to copy backup of production to development server.
Note: pg_dump,psql, scp needs to be executed from Linux shell. Not from psql tool.
On Source PostgreSQL Server:
1. Take backup of table using pg_dump.
2. SCP the backup file from source to target(copy backup file from source to target)
On Target PostgreSQL Server:
3. Restore the table using psql tool
4. Verify the table size with production
Step 1. Take backup of table using pg_dump
Here, I am going to take backup of the table ‘student’ from the database ‘r2schools’ on source PostgreSQL server.
pg_dump -d r2schools -t student > /opt/PGBACKUPS/student_table.sql
Step 2. SCP the backup file from source to target
scp -p student_table.sql root@mongodb2:/home/r2schools/pgbackups
Step 3. Restore the table using psql tool
Connect to the target PostgreSQL server and navigate to backup file scp location and verify production backup copied or not.
postgres@mongodb2:/home/r2schools/pgbackups$ pwd /home/r2schools/pgbackups postgres@mongodb2:/home/r2schools/pgbackups$ ls student_table.sql
Now perform restore operation of table ‘student’ on target server using psql command. Here my target server hostname is ‘mongodb2’.
psql -d r2schools < student_table.sql
Where -d is the database in which table exists.
Step 4. Verify the table size with production
Connect to the r2schools database and verify the total row count of table on source and target PostgreSQL servers.
On Source:
On Target:
Both source and target servers contains same number of rows.
So in this article, we have successfully performed backup and restore of PostgreSQL table.