Category Archives: PostgreSQL

DBA, PostgreSQL

pgbackup: Backup PostgresSQL

Mediante barman (pgbarman) podremos automatizar los backups y restauraciones de bases de datos PostgreSQL

En CentOS, simplemente deberemos instalar el paquete desde EPEL y configurar unos mínimos (/etc/barman/barman.conf)

[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
configuration_files_directory = /etc/barman.d

También deberemos configurar acceso mediante claves SSH (sin contraseña) en ambas direcciones desde el servidor de barman al usuario de la base de datos de las instancias que queramos hacer backup.

La configuración de los backups los haremos en ficheros independientes en el directorio /etc/barman.d para mayor comodidad, por ejemplo /etc/barman.d/pgm.conf

[pgm]
description = "postgres master"
ssh_command = ssh postgres@192.168.56.29
conninfo = host=192.168.56.29 user=postgres
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 30 days
wal_retention_policy = main

Mediante el comando show-server podremos buscar en que directorio deberemos dejar los archivados:

# barman show-server pgm | grep incoming_wals_directory
	incoming_wals_directory: /var/lib/barman/pgm/incoming

Mediante el modulo de puppet eyp-postgres podremos configurar un archive command para hacer rsync desde la base de datos al servidor.

	class { 'postgresql':
		wal_level => 'hot_standby',
		max_wal_senders => '3',
		checkpoint_segments => '8',
		wal_keep_segments => '8',
		archive_mode => true,
		archive_command_custom => 'rsync -a %p barman@192.168.56.31:/var/lib/barman/pgm/incoming/%f',
	}

Una vez este todo configurado podremos hacer copias de seguridad mediante backup, por ejemplo:

# barman backup pgm 
Starting backup for server pgm in /var/lib/barman/pgm/base/20160415T165403
Backup start at xlog location: 0/3000020 (000000010000000000000003, 00000020)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup end at xlog location: 0/30000E0 (000000010000000000000003, 000000E0)
Backup completed

Supongamos que tenemos ya el backup y hacemos lo siguiente en la base de datos:

postgres=# insert into test values('fuckthesystem');
INSERT 0 1
postgres=# select * from test;
       txt        
------------------
 sakura
 enlargeyourpenis
 fuckthesystem
(3 rows)


postgres=# delete from test;
DELETE 2
postgres=# select * from test;
 val 
-----
(0 rows)

postgres=#

Podremos ver el listado de backups disponibles mediante list-backup:

# barman list-backup pgm
pgm 20160415T165403 - Fri Apr 15 14:54:04 2016 - Size: 19.3 MiB - WAL Size: 0 B

Para poder hacer la restauración necesitaremos algunos detalles del backup, los veremos mediante show-backup:

# barman show-backup pgm latest
Backup 20160415T165403:
  Server Name            : pgm
  Status                 : DONE
  PostgreSQL Version     : 90216
  PGDATA directory       : /var/lib/pgsql/9.2/data

  Base backup information:
    Disk usage           : 19.3 MiB
    Timeline             : 1
    Begin WAL            : 000000010000000000000003
    End WAL              : 000000010000000000000003
    WAL number           : 0
    Begin time           : 2016-04-15 14:54:01.835645+02:00
    End time             : 2016-04-15 14:54:04.514398+02:00
    Begin Offset         : 32
    End Offset           : 224
    Begin XLOG           : 0/3000020
    End XLOG             : 0/30000E0

  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : None

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20160415T142747
    Next Backup          : - (this is the latest base backup)

Necesitamos el nombre del backup y el “Begin time” exacto. Procedemos primero a parar la base de datos:

# /etc/init.d/postgresql-9.2 stop

A continuación indicamos a barman que recupere desde dicho backup indicando el begin time mediante –target-time y el nombre del backup. Podemos 

# barman recover  --target-time "2016-04-15 14:54:01.835645+02:00" --remote-ssh-command="ssh postgres@192.168.56.29" pgm 20160415T142747 /var/lib/pgsql/9.2/data
Processing xlog segments for pgm
	000000010000000000000001
	000000010000000000000002
	000000010000000000000003
	000000010000000000000003.00000020.backup
	000000010000000000000004
Starting remote restore for server pgm using backup 20160415T165403 
Destination directory: /var/lib/pgsql/9.2/data
Copying the base backup.
Copying required wal segments.
The archive_command was set to 'false' to prevent data losses.

Your PostgreSQL server has been successfully prepared for recovery!

Please review network and archive related settings in the PostgreSQL
configuration file before starting the just recovered instance.

WARNING: Before starting up the recovered PostgreSQL server,
please review also the settings of the following configuration
options as they might interfere with your current recovery attempt:

    external_pid_file = '/var/lock/subsys/postgresql-9.2'			# write an extra PID file

Una vez recuperado, simplemente deberemos levantar la instancia:

/etc/init.d/postgresql-9.2 start

Si nos conectamos, veremos que tenemos los datos en el momento de hacer el backup:

[root@pgm ~]# psql -U postgres
psql (9.2.16)
Type "help" for help.

postgres=# select * from test;
       txt        
------------------
 sakura
 enlargeyourpenis
(2 rows)

postgres=#

Tags:

Consulting, Database, Linux, MySQL, NoSQL, Open Source, PostgreSQL

Bull Services Facilitate Adoption of Open Source PostgreSQL

Bull HN Information Systems is rolling out IT support services with the launch of its MOVE IT (Modernize, Optimize, Virtualize and Economize Information Technology) campaign to showcase products and services that it recently announced and plans to announce in the future. These products and services help customers derive maximum value from their legacy IT investments and get the most out of their IT operations while opening enterprise data to the cloud and mobile devices.

Bull’s newest MOVE IT service offerings are PostgreSQL support subscriptions; database design and build assessments; database performance and tuning services; and forms and reports migration services. These service offerings support migration to PostgreSQL—recognized as the world’s most advanced open source database—enabling organizations to reduce costs and open enterprise data to the cloud and virtualized environments.

According to Bull’s Data Migration Business Unit Director Jim Ulrey, “MOVE IT services and software help free companies from high licensing and maintenance costs, and offer both dramatic operational efficiencies and the agility required to flourish in competitive business environments.

“We developed MOVE IT enterprise solutions including database migration services, software and our newest support services to meet the needs of IT departments that prefer to manage work internally, as well as those that prefer to outsource—whether due to skill sets, resources or project objectives,” concluded Ulrey.

Bull’s MOVE IT products and services work effectively standalone by providing solutions to specific challenges, and they’re also engineered to work together to provide enterprise IT clients with multiple benefits. From cost-saving database migrations from Oracle to flexible open source PostgreSQL, to LiberTP™ software to migrate transaction-processing applications, Bull’s solutions open enterprise data to modern environments that support the cloud, virtualized environments and mobile devices. Most importantly, Bull can help free companies from high licensing and maintenance costs, offer dramatic operational efficiencies and the agility required to flourish in competitive business environments.