How to Backup and Restore PostgreSQL 9.5.1 Database

Change your current user to postgres

root@ubuntu:~# su postgres
postgres@ubuntu:/root$ cd /tmp/

Listing all database inside postgresql

postgres@ubuntu:/tmp$ psql -l
List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
newdes    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
|          |          |             |             | =c/postgres
(4 rows)

Backing up single database (using pg_dump not pg_dumpall)

For your info, you can't restore plain backup using pg_restore, but you must using psql tool, for more information about this, you can read this.

Plain Backup (text file)
you can open this backup using text editor, like vi, nano, mcedit, pico, etc.

postgres@ubuntu:/tmp$ pg_dump newdes > /tmp/newdes_30072016.sql

Compressed binary format

postgres@ubuntu:/tmp$ pg_dump -Fc newdes > /tmp/newdes_30072016.bak

Tarball file
can restoring using pgadmin

postgres@ubuntu:/tmp$ pg_dump -Ft newdes > /tmp/newdes_30072016.tar

postgres@ubuntu:/tmp$ ls -al /tmp/
total 10592
drwxrwxrwt  3 www-data users      20480 Jul 30 10:08 .
drwxr-xr-x 22 root     root        4096 Jul 20 16:19 ..
-rwxrw-rw-  1 root     kraken   6059213 Jul 30 08:12 kraken.backup
drwx------  2 root     root        4096 Jul 30 09:56 mc-root
-rw-rw-r--  1 postgres postgres  465051 Jul 30 10:08 newdes_30072016.bak
-rw-rw-r--  1 postgres postgres 2112274 Jul 30 10:08 newdes_30072016.sql
-rw-rw-r--  1 postgres postgres 2174464 Jul 30 10:08 newdes_30072016.tar

Restoring backup database

postgres@ubuntu:/tmp$ psql --version
psql (PostgreSQL) 9.5.1

Create empty database
From root user (or another user)

root@ubuntu:~# createdb -h localhost -p 5432 -U postgres newdes
Password: ****

Or from postgres user

root@ubuntu:~# su postgres
postgres@ubuntu:/tmp$ psql -l
postgres@ubuntu:/tmp$ createdb newdes;

Listing all database inside postgresql

postgres@ubuntu:/tmp$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 newdes    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
(4 rows)

Restoring Plain backup (database already exist)

Alternative command 1

postgres@ubuntu:/tmp$ createdb newdes;
postgres@ubuntu:/tmp$ psql newdes -f newdes_30072016.sql
SET
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
----

Alternative command 2

postgres@ubuntu:/tmp$ createdb newdes;
postgres@ubuntu:/tmp$ psql -f newdes_30072016.sql template1
SET
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
----

Alternative command 3

postgres@ubuntu:/tmp$ createdb newdes;
postgres@ubuntu:/tmp$ psql -U postgres < newdes_30072016.sql
SET
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
----

Alternative command 4

postgres@ubuntu:/tmp$ createdb newdes;
postgres@ubuntu:/tmp$ psql -f newdes_30072016.sql postgres
SET
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
----

Restoring compressed binary format

pg_restore -Fc newdes_30072016.bak

Restoring tarball file

pg_restore -Ft newdes_30072016.tar

Restoring Plain backup (database NOT exist)

Restoring compressed binary format

pg_restore -Fc -C newdes_30072016.bak

Restoring tarball file

pg_restore -Ft -C newdes_30072016.tar