How to view and backup your postgresql database

You can view and backup your postgresql database using

A – the pgadmin3 interface

B – a command line


A – How to view and backup your postgresql database using the pgadmin3 interface

If pgadmin3 is not install, install it as follows

$ su

# yum install pgadmin3

# exit

# pgadmin3

Before launching pgadmin3, you must have postgres sql installed (see link related to installation of CAR) and CAR must have been launched so that events are recorded by CAR in the db.

Click on the top left button to connect and fill this fields as follows:

name: demcare
host: localhost

Then using the object browser window on the left, extend the following items:

a) Server Groups

b) Servers

c) demcare(localhost:5432)

d) Databases

e) demcare

f) Schemas

g) dm

h) Tables

i) dematcare_event_t

To back up your database, right clik on c) and select Backup. Enter a filename.sql and luanch the backup.

To view the content of your database, right click on i) and select view data.


B – How to backup your postgresql database using a command line

B1 – How to access the Database

Run psql as postgres user (As root) :

su postgres -c psql

The prompt will change to postgres=#.

Connect to the database, for instance on demcare :

\c demcare

Run a query to display the content of a table, for instance on dm.dematcare_event_t :

select * from dm.dematcare_event_t ;

To quit psql use the following command :


B2 – Database backup

To save and restore the content of a database (e.g. tables and data), use the following lines :

To make a backup of the database :

pg_dump demcare > /path/to/backup.sql

To restore the database from a backup :

psql demcare < /path/to/backup.sql

For a large database, we can use compression tools :

To make a backup :

pg_dump demcare | gzip > /path/to/backup.sql.gz

To restore the backup :

gunzip -c /path/to/backup.sql.gz | psql demcare

To save and restore all database in the cluster (role, rules, database content), use the following lines :

To make a backup of the cluster :

pg_dumpall > /path/to/backup.sql

To restore the cluster from a backup :

psql -f /path/to/backup.sql postgres


To get the database in human understandable format :

select pg_size_pretty(pg_database_size('demcare'));

The database is stored in the file postgresql.conf, see the data_directory parameter. The value by default is ConfigDir (location /var/lib/pgsql/9.2/data/)

Leave a Reply