Beginning PostgreSQL

As a MySQL user for a number of years I needed a translation tutorial from MySQL to PostgreSQL. So to get started on CentOS:

Installation

yum install postgresql-server

If you also want the phpPgAdmin WUI:

yum install phpPgAdmin php-pgsql httpd

Create the configuration file and initialise the database:

cp /usr/share/pgsql/postgresql.conf.sample /var/lib/pgsql/data/postgresql.conf
/etc/init.d/postgresql initdb

Make changes to the configuration:

vi /var/lib/pgsql/data/postgresql.conf

listen_addresses = ‘*’

Create some symlinks to help eejits like me who expect conf files under /etc:

mkdir /etc/postgresql
ln -s /var/lib/pgsql/data/pg_hba.conf /etc/postgresql/pg_hba.conf
ln -s /var/lib/pgsql/data/pg_ident.conf /etc/postgresql/pg_ident.conf
ln -s /var/lib/pgsql/data/postgresql.conf /etc/postgresql/postgresql.conf

Set the service to start on boot:

/sbin/chkconfig postgresql on
/etc/init.d/postgresql start

User Management

To manage Postgres as the ‘root’ user

su – postgres

Running psql will give the traditional command-line interface or you can pass commands (-c) as follows:

/usr/bin/psql -c “alter user postgres with password ‘password'”

The default method for user management in postgres is to create a local unix user account. I would prefer to stick with the user management I am used to in MySQL where we connect with a username and password combination.

vi /var/lib/pgsql/data/pg_hba.conf

At the bottom of the file comment out the existing configuration and add the following:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          ident
local   all         all                               md5
host    all         all         123.123.0.0/16        md5
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5

Create a User and Database

To create a database and a user with the same name as the database and grant privileges to that user on that database:

su – postgres
psql
postgres=# CREATE USER jonny_test WITH PASSWORD ‘topsecret’;
postgres=# CREATE DATABASE jonny_test;
postgres=# GRANT ALL PRIVILEGES ON DATABASE jonny_test TO jonny_test;
postgres=# \q

Connect to the Database

psql -U jonny_test2 –password
Password for user jonny_test2:
psql (8.4.9)
Type “help” for help.
jonny_test2=> \q

Changing a User Password

su – postgres
/usr/bin/psql -c “ALTER USER joe WITH PASSWORD ‘topsecret'”

Backing Up Databases

For a full backup :

pg_dumpall > all.dbs.out

A script to backup each database:

#!/bin/bash
DIR=/var/backups/viper/postgresql
LIST=$(psql -l | grep UTF8 | awk ‘{ print $1}’ )
for d in $LIST
do
pg_dump $d | gzip -c >  $DIR/$d.out.gz
done

Restoring Databases

For extra info about backing up and restoring PostgreSQL, but for a restore of a single database dump :

psql dbname < infile

Command Line Fu

There are also direct binaries for creating users and databases which may come in useful, for example:

/usr/bin/createuser jonny
su – jonny
/usr/bin/createdb testdb
/usr/bin/psql -l

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">