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.
postgres@mongodb1:/opt/PGBACKUPS$ pg_dump -U postgres -W -d r2schools > /opt/PGBACKUPS/r2schools$(date +%Y-%m-%d_%H_%M_%S).sql Password: postgres@mongodb1:/opt/PGBACKUPS$ ls r2schools2020-01-07_10_39_00.sql postgres@mongodb1:/opt/PGBACKUPS$
Step 2. SCP the backup file from source to target
If source and target servers are same, then skip this step.
postgres@mongodb1:/opt/PGBACKUPS$ scp -p r2schools2020-01-07_10_39_00.sql root@mongodb2:/home/r2schools/backups/ root@mongodb2's password: r2schools2020-01-07_10_39_00.sql 100% 16KB 7.7MB/s 00:00 postgres@mongodb1:/opt/PGBACKUPS$
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.
pg_dumpall -r > roles$(date +%Y-%m-%d_%H_%M_%S).sql
Where
-r means take only backup of roles.
Step4. Restore the database using psql or pg_restore command.
1. Load the database r2schools on target server using either psql or pg_restore command.
psql -d r2schools < r2schools2020-01-07_10_39_00.sql
Output of above command:
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$
Step5: Restore the roles taken in step 3.
psql < roles.sql
Step6: Connect to the database.
\c r2schools \dt
For video tutorial visit below link: