How To Backup And Restore PostgreSQL Database

In this tutorial, I will share how to export a database using postgres user.

To export entire database into a file, execute the below command from the terminal.

Backup PostgreSQL Database

pg_dump -d tekspace_db_dev -U postgres -h localhost > tekspace_dev.dump.txt

Here are the options I used to export a database to a file.

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

Restore PostgreSQL Database

Now connect to your new server and execute the below command and replace [database_name].

psql -h localhost -U postgres -W [database_name] < tekspace_dev.dump.txt

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "/var/run/postgresql")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "rahil")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

That’s all!