Guía rápida de instalación y administración de PostgreSQL 9.x (CentOS6.x)

En este howto configuraremos la versión de PostgreSQL 9.3 la mas reciente hasta ahora.

 - Configurando repositorio para excluir los paquetes "postgresql" del repo Base de CentOS.

$ vim /etc/yum.repos.d/CentOS-Base.repo
[base]
...
...
exclude=postgresql*

[updates]
...
...
exclude=postgresql*

- Instalación de repositorio Postgresql. Puedes buscar el repositorio mas adecuado a tu arquitectura y la versión mas actual en http://yum.postgresql.org/repopackages.php

$ cd /tmp/
$ wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
$ yum localinstall pgdg-centos93-9.3-1.noarch.rpm

- Instalando los paquetes del servicio.

$ yum install postgresql93-server postgresql93-contrib

- Inicializando la base de datos

$ service postgresql-9.3 initdb

Iniciando la base de datos:                                [  OK  ]
$ service postgresql-9.3 start

- Configurando auto-arranque del servicio y runlevels

$ chkconfig postgresql-9.3 on
$ chkconfig --list postgresql-9.3
postgresql-9.1  0:desactivado   1:desactivado   2:activo        3:activo        4:activo        5:activo        6:desactivado

- Configuración básica del servicio

$ vim /var/lib/pgsql/9.3/data/postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 120
superuser_reserved_connections = 3

shared_buffers = 1024MB   # 25% RAM

temp_buffers = 16MB   # Default 8MB

work_mem = 2MB    # min 64kB deft 1MB
maintenance_work_mem = 32MB  # min 1MB deft 16MB
max_stack_depth = 4MB   # min 100kB deft 2MB

effective_cache_size = 128MB  # Deft 128MB. Optimo %70 Ram max

log_line_prefix = '< %t %u %d %r >'

- Optimizar la configuración. Si lo que queremos es tener una mejor configuración de PostgreSQL de acuerdo a nuestro Hardware disponible le recomiendo para esto la herramienta pgtune . Esta herramienta te genera una configuración tomando en cuenta tu Hardware. Lo instalamos de la siguiente forma. (Necesitas el repositorio EPEL)

$ sudo yum install pgtune --enablerepo epel

$ export PGDATA=/var/lib/pgsql/9.3/data/

$ sudo pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune

Se genera un nuevo archivo de configuración nombrado postgresql.conf.pgtune que puedes ya usar para tu servicio. Resguardamos la configuración actual, luego sobrescribimos con nuestra nueva configuración y reiniciamos el servicio.

$ sudo cp $PGDATA/postgresql.conf $PGDATA/postgresql.conf.old

$ sudo cp $PGDATA/postgresql.conf.pgtune $PGDATA/postgresql.conf

$ sudo service postgresql-9.3 restart

- Configuración de accesos. Configure a su criterio los accesos de conexion que desea tener.

$ sudo vim /var/lib/pgsql/9.3/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             10.0.0.0/8              md5
host    all             all             192.168.0.0/24          md5

- Firewall (iptables). Abrimos el puerto del servicio

$ vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

$ service iptables restart

Administracion

- Entrar como superususario

$ sudo -u postgres psql postgres

- Crear usuario o ROL

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'ultrapassword' NOSUPERUSER INHERIT NOCREATEROLE NOREPLICATION CREATEDB;

- Cambiar contraseña de un usuario

postgres=# ALTER USER demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password'; 

- Cambiar privilegios

postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

- Eliminar ROL

postgres=# DROP ROLE demorole1;

- Crear base de datos

postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

- Asignar una DB a un usuario

postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

- Eliminar DB

postgres=# DROP DATABASE demodb1;

- Respaldar una base de datos

$ pg_dump -h localhost -p 5432 -U username -F c -b -v -f "my_db.backup" db_to_backup

- Restaurar base de datos

$ pg_restore -h localhost -d db_to_restore -U someuser somedb.backup

Mas información sobre la administración de PostgreSQL les dejo este Cheat Sheet de DZone Refcard.

- http://refcardz.dzone.com/refcardz/essential-postgresql