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.
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
