How to Back up and Restore a PostgreSQL Database

Mi Guoliang

I show you how to use pg_dump and psql commands to backup and restore PostgreSQL databases in the command line. And this tutorial also includes basic usages of these tools and software.

Install PostgreSQL image

  1. Run touch docker-compose.yml in your prompt, and paste following codes in it:

     version: '3'
     services:
         db:
             image: postgres:10-alpine
             container_name: db
             environment:
             POSTGRES_DB: "mrrs"
             POSTGRES_USER: "postgres"
             POSTGRES_PASSWORD: "atp0769AT"
             ports:
                 - 5432:5432
         adminer:
             image: adminer
             restart: always
             ports:
                 - 8080:8080
    
  2. Run docker-compose up -d in the same directory as docker-compose.yml to download and run PostgreSQL and Adminer in the backend.

     $ docker-compose up -d
     Creating network "demo_default" with the default driver
     Creating demo_adminer_1 ... done
     Creating db             ... done
    

Create a demo database

  1. Open http://localhost:8080 in your browser.
  2. Login to PostgreSQL

  3. Create an empty database

  4. Enter the database name, and save

Create a simple table and insert a record into it

  1. Open SQL command window.

  2. Paste and run the follwing codes:

     create table if not exists ofVersion
     (
         "name"    varchar(50) not null,
         "version" integer     not null,
         constraint ofVersion_pk primary key ("name")
     );
    
     insert into ofVersion ("name", "version")
     values ('openfront', 1);
    

    These SQL commands create a table named ofVersion, and insert a record to ofVersion.

Backup a database

  1. Run docker exec -it db /bin/bash to enter the PostgreSQL image. If you are not familiar Docker, this step can be understood as entering another computer which installs PostgreSQL only.

     $ docker exec -it db /bin/bash
     bash-5.0#
    
  2. Run pg_dump -U postgres -W -d demo > backup.sql to backup the database I just created by Adminer. Arguments in this command means:

    • -U: User name to connect as.
    • -W: Force pg_dump to prompt for a password before connecting to a database.
    • -d: Specifies the name of the database to connect to.

    After that, you can get a file named backup.sql in / in the docker image. You can verify the SQL file by running command:

     bash-5.0# ls backup.sql
     backup.sql
    

Make a simple change to the demo database

I drop ofVersion table to simulate a misoperation, and I need to recover the database from a backup file.

Run the following codes in the SQL Command Window to drop ofVersion table:

drop table ofversion;

Restore a database

Run psql -U postgres -W -d demo -f backup.sql to restore the database I just dumped. Arguments in this command means:

bash-5.0# psql -U postgres -W -d demo -f backup.sql
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
COPY 1
ALTER TABLE

Verify the restored database

Run the following codes in SQL Command Window:

select * from ofversion;

Conclusion

This tutorial explains a situation that developers need to backup and restores their database completely; however, in a production environment, developers need not back up and restore their database completely every time. Generally, developers only need to back up the data of their database frequently, and backup the entire database (includes structures and data) in a more extended period.

If you like my share, you can:

• Follow My Programming Experiences Page on Facebook.

• Follow My Twitter.

• Subscribe to my mail list.