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 :
\q
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
PS:
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/)
