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
