En este howto configuraremos la version 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*
Instalacion de repositorio Postgresql
Puedes buscar el repositorio mas adecuado a tu arquitectura y la version 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
$ 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 autoarranque 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
Configuracion basica 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 optimizacion de la configuracion de PostgreSQL de acuerdo a nuestro hardware disponible le recomiendo para esto la herramienta pgtune http://pgfoundry.org/projects/pgtune. Esta herramienta te genera una configuracion tomando en cuenta tu hardware. Lo instalamos de la siguiente forma.
$ 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 configuracion nombrado postgresql.conf.pgtune que puedes ya usar para tu servicio. Resguardamos la configuracion actual, luego sobreescribimos con nuestra nueva configuracion 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
Configuracion 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 usuario privilegiado
$ 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
