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.
In this tutorial, we will see how to take backup and restore a PostgreSQL Database.
It is administrator regular activity to take backup and restore a database from production server to development or UAT servers.
PostgreSQL Tools for Backup and Restore:
1. pg_dump: Extract a PostgreSQL database into a script file or other archive file.
2. pg_dumpall: Extract a PostgreSQL database cluster into a script file.
3. psql: PostgreSQL interactive terminal used to load backup taken using pg_dump.
4. pg_restore: restore a PostgreSQL database from an archive file created by pg_dump.
Note: pg_dump,psql, pg_restore and pg_dumpall needs to be executed from Linux shell. Not from psql tool.
Steps to backup and restore a PostgreSQL Database:
On Source PostgreSQL Server:
1. Take backup of database using pg_dump.
2. SCP the backup file from source to target(copy backup file from source to target)
On Target PostgreSQL Server:
3. Take backup privileges on target server using pg_dumpall
4. Restore the database using psql or pg_restore command.
5. Now, restore the privileges(roles) taken in the step 3.
6. Test the connection to the loaded database.
Step 1. Take backup of database using pg_dump
In this tutorial, i am going to take backup of r2schools from the source PostgreSQL server ‘mongodb1’ and restore the r2schools database into target PostgreSQL server ‘mongodb2’.
1. Create a directory to to store backup files
Create a directory to store backup files. I am creating directory with the name PGBACKUPS in /opt/ path. You create backup directory where you have sufficient space to store backup of database.
postgres@mongodb1:/opt$ mkdir PGBACKUPS
postgres@mongodb1:/opt$ cd PGBACKUPS/
2. Now, run the pg_dump command as shown below.
p pg_dump -U postgres -W -d r2schools > /opt/PGBACKUPS/r2schools$(date +%Y-%m-%d_%H_%M_%S).sql
Where
-U postgres–> User name to connect as.
-d r2schools –> Database to take backup.
-W –> Password to be provided for the user.
$(date +%Y-%m-%d_%H_%M_%S) — Current date in YYYY-mm-dd_H_M_S format.
3. Once execute it will ask us the password. Provide the password for the user. Then, backup file created at specified location. In my backup file created at /opt/PGBACKUPS. Lets verify.
Where
r2schools2020-01-07_10_39_00.sql is the backup file.
root@mongodb2:/home/r2schools/backups/ is the username@hostname:target path to copy backup files.
On Target PostgreSQL Server:
Connect to Target server. Verify the backup files moved to successfully or not.
root@mongodb2:/home/r2schools/backups# ls
r2schools2020-01-07_10_39_00.sql
root@mongodb2:/home/r2schools/backups# pwd
/home/r2schools/backups
Step3. Take backup privileges on target server using pg_dumpall
1. Take backup roles on the target server using pg_dumpall. Here my target server is mongodb2. Execute the below command from Linux host.
postgres@mongodb2:/home/r2schools/backups$ psql -d r2schools < r2schools2020-01-07_10_39_00.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 2
COPY 0
COPY 12
COPY 4
COPY 12
COPY 7
COPY 0
COPY 21
COPY 21
COPY 21
COPY 0
COPY 3
COPY 4
COPY 5
COPY 27
COPY 6
COPY 1
COPY 3
COPY 0
COPY 0
ALTER TABLE
ALTER TABLE
ALTER TABLE
GRANT
postgres@mongodb2:/home/r2schools/backups$