PostgreSQL

From SIPfoundry sipx, The Open Source SIP PBX for Linux - Calivia

Jump to: navigation, search

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

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

Retrieved from "http://sipx-wiki.calivia.com/index.php/PostgreSQL"
Personal tools