PostgreSQL
From SIPfoundry sipXecs IP PBX, The Open Source SIP PBX for Linux - Calivia
Contents |
Database Maintenance
| Code: Drop a Database from the command line |
su - postgres dropdb database_name |
| Code: Create a new user from the command line |
# become the postgres user su - postgres # create a new user createuser --pwprompt Enter name of user to add: username Enter password for new user: password Enter it again: password Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) n CREATE USER |
PostgreSQL doesn't need to be restarted to load changed configuration settings.
| Code: Reload altered configuration files (i.e. pg_hba.conf) |
su /etc/init.d/postgresql reload |
Permissions
PostgreSQL 8 allows access from localhost by default.
| File: /var/lib/postgresql/data/pg_hba.conf |
# This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of seven forms: # # local DATABASE USER METHOD [OPTION] # host DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] # hostssl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] # hostnossl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] # host DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] # hostssl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] # hostnossl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] |
Administration Tools
Note: Remember to add access permissions to pg_hba.conf and make sure postgres is listening on an external interface if you want to administer from a remote client (pgAdmin3).
- pgAdmin III: pgAdmin III is a powerful administration and development platform for the PostgreSQL database, free for any use. The application is running under GNU/Linux, FreeBSD and Windows 2000/XP. http://www.pgadmin.org/pgadmin3/index.php
- phpPgAdmin: Web PHP GUI for PostgreSQL administration. http://phppgadmin.sourceforge.net/
Upgrade from 7.4.x to 8.0
There is no automatic upgrade procedure to upgrade existing databases from 7.4.x to 8.0. In production environments, proper database backup must be performed prior to upgrading to version 8.
Upgrade procedure
First we need to dump all databases. sipX uses two databases 'PDS' and 'SDS'. Check documentation for pg_dump and pg_restore for more sophisticated options. The following procedure should work with small databases.
| Code: Dump databases |
# become the postgres user su - postgres # dump databases to files pg_dump SDS > SDS-dump pg_dump PDS > PDS-dump exit # stop PostgreSQL /etc/init.d/postgresql stop |
The ebuild will not upgrade PostgreSQL with a directory from an old version.
| Code: Rename PostgreSQL directory and emerge PostgreSQL |
mv /var/lib/postgresql/ /var/lib/postgresql-7.4 emerge --ask dev-db/postgresql |
| Code: Create /var/lib/postgresql directory and start PostgreSQL |
ebuild /usr/portage/dev-db/postgresql/postgresql-8.0.1-r2.ebuild config /etc/init.d/postgresql start |
| Code: Restore databases |
# restore PDS createdb PDS psql PDS < PDS-dump # restore SDS createdb SDS psql SDS < SDS-dump |
Configuration
Make sure you carry over all custom configuration options from your old version.
Note: Version 8 has TCP/IP sockets enabled by default.
Note: No custom access permissions need to be set in pg_hba.conf with version 8. Access from localhost is granted by default for all databases.
The old data directory can safely be removed.
Complete Dump and Restore
For diagnostic purposes, the entire sipXconfig database can be dumped in the form of SQL statements that would re-create its current state using this command:
| Code: Dump sipXconfig database |
pg_dump -d SIPXCONFIG -U postgres >file |
In the unlikely event you want to restore the entire sipXconfig database from a dump, you can use these commands:
| Code: Restore sipXconfig database |
|
{{{2}}} |
