{"id":29554,"date":"2016-04-19T08:04:18","date_gmt":"2016-04-19T08:04:18","guid":{"rendered":"http:\/\/systemadmin.es\/?p=5085"},"modified":"2016-04-19T08:04:18","modified_gmt":"2016-04-19T08:04:18","slug":"pgbackup-backup-postgressql","status":"publish","type":"post","link":"https:\/\/icloud.pe\/blog\/pgbackup-backup-postgressql\/","title":{"rendered":"pgbackup: Backup PostgresSQL"},"content":{"rendered":"<p>Mediante <strong>barman<\/strong> (<strong>pgbarman<\/strong>) podremos automatizar los backups y restauraciones de bases de datos <strong>PostgreSQL<\/strong><\/p>\n<p><!-- more --><\/p>\n<p>En <strong>CentOS<\/strong>, simplemente deberemos instalar el paquete desde <strong>EPEL<\/strong> y configurar unos m\u00ednimos (<strong>\/etc\/barman\/barman.conf<\/strong>)<\/p>\n<pre>\r\n[barman]\r\nbarman_home = \/var\/lib\/barman\r\nbarman_user = barman\r\nlog_file = \/var\/log\/barman\/barman.log\r\ncompression = gzip\r\nconfiguration_files_directory = \/etc\/barman.d\r\n<\/pre>\n<p>Tambi\u00e9n deberemos configurar <strong>acceso mediante claves SSH<\/strong> (sin contrase\u00f1a) <strong>en ambas direcciones<\/strong> desde el servidor de barman al usuario de la base de datos de las instancias que queramos hacer backup.<\/p>\n<p>La <strong>configuraci\u00f3n de los backups<\/strong> los haremos en ficheros independientes en el directorio \/etc\/barman.d para mayor comodidad, por ejemplo <strong>\/etc\/barman.d\/pgm.conf<\/strong><\/p>\n<pre>\r\n[pgm]\r\ndescription = \"postgres master\"\r\nssh_command = ssh postgres@192.168.56.29\r\nconninfo = host=192.168.56.29 user=postgres\r\nretention_policy_mode = auto\r\nretention_policy = RECOVERY WINDOW OF 30 days\r\nwal_retention_policy = main\r\n<\/pre>\n<p>Mediante el comando <strong>show-server<\/strong> podremos buscar en que directorio deberemos dejar los archivados:<\/p>\n<pre>\r\n# barman show-server pgm | grep incoming_wals_directory\r\n\tincoming_wals_directory: \/var\/lib\/barman\/pgm\/incoming\r\n<\/pre>\n<p>Mediante el modulo de puppet <a href=\"https:\/\/github.com\/AtlasIT-AM\/eyp-postgresql\">eyp-postgres<\/a> podremos configurar un archive command para hacer rsync desde la base de datos al servidor.<\/p>\n<pre>\r\n\tclass { 'postgresql':\r\n\t\twal_level => 'hot_standby',\r\n\t\tmax_wal_senders => '3',\r\n\t\tcheckpoint_segments => '8',\r\n\t\twal_keep_segments => '8',\r\n\t\tarchive_mode => true,\r\n\t\tarchive_command_custom => 'rsync -a %p barman@192.168.56.31:\/var\/lib\/barman\/pgm\/incoming\/%f',\r\n\t}\r\n<\/pre>\n<p>Una vez este todo configurado podremos hacer copias de seguridad mediante <strong>backup<\/strong>, por ejemplo:<\/p>\n<pre>\r\n# barman backup pgm \r\nStarting backup for server pgm in \/var\/lib\/barman\/pgm\/base\/20160415T165403\r\nBackup start at xlog location: 0\/3000020 (000000010000000000000003, 00000020)\r\nCopying files.\r\nCopy done.\r\nAsking PostgreSQL server to finalize the backup.\r\nBackup end at xlog location: 0\/30000E0 (000000010000000000000003, 000000E0)\r\nBackup completed\r\n<\/pre>\n<p>Supongamos que tenemos ya el backup y hacemos lo siguiente en la base de datos:<\/p>\n<pre>\r\npostgres=# insert into test values('fuckthesystem');\r\nINSERT 0 1\r\npostgres=# select * from test;\r\n       txt        \r\n------------------\r\n sakura\r\n enlargeyourpenis\r\n fuckthesystem\r\n(3 rows)\r\n\r\n\r\npostgres=# delete from test;\r\nDELETE 2\r\npostgres=# select * from test;\r\n val \r\n-----\r\n(0 rows)\r\n\r\npostgres=# \r\n<\/pre>\n<p>Podremos ver el listado de backups disponibles mediante <strong>list-backup<\/strong>:<\/p>\n<pre>\r\n# barman list-backup pgm\r\npgm 20160415T165403 - Fri Apr 15 14:54:04 2016 - Size: 19.3 MiB - WAL Size: 0 B\r\n<\/pre>\n<p>Para poder hacer la restauraci\u00f3n necesitaremos algunos detalles del backup, los veremos mediante <strong>show-backup<\/strong>:<\/p>\n<pre>\r\n# barman show-backup pgm latest\r\nBackup <strong>20160415T165403<\/strong>:\r\n  Server Name            : pgm\r\n  Status                 : DONE\r\n  PostgreSQL Version     : 90216\r\n  PGDATA directory       : \/var\/lib\/pgsql\/9.2\/data\r\n\r\n  Base backup information:\r\n    Disk usage           : 19.3 MiB\r\n    Timeline             : 1\r\n    Begin WAL            : 000000010000000000000003\r\n    End WAL              : 000000010000000000000003\r\n    WAL number           : 0\r\n<strong>    Begin time           : 2016-04-15 14:54:01.835645+02:00<\/strong>\r\n    End time             : 2016-04-15 14:54:04.514398+02:00\r\n    Begin Offset         : 32\r\n    End Offset           : 224\r\n    Begin XLOG           : 0\/3000020\r\n    End XLOG             : 0\/30000E0\r\n\r\n  WAL information:\r\n    No of files          : 0\r\n    Disk usage           : 0 B\r\n    Last available       : None\r\n\r\n  Catalog information:\r\n    Retention Policy     : VALID\r\n    Previous Backup      : 20160415T142747\r\n    Next Backup          : - (this is the latest base backup)\r\n<\/pre>\n<p>Necesitamos el nombre del backup y el &#8220;Begin time&#8221; exacto. Procedemos primero a parar la base de datos:<\/p>\n<pre>\r\n# \/etc\/init.d\/postgresql-9.2 stop\r\n<\/pre>\n<p>A continuaci\u00f3n indicamos a barman que recupere desde dicho backup indicando el <strong>begin time<\/strong> mediante <strong>&#8211;target-time<\/strong> y el <strong>nombre del backup<\/strong>. Podemos <\/p>\n<pre>\r\n# barman recover  --target-time \"<strong>2016-04-15 14:54:01.835645+02:00<\/strong>\" --remote-ssh-command=\"ssh postgres@192.168.56.29\" pgm <strong>20160415T142747<\/strong> \/var\/lib\/pgsql\/9.2\/data\r\nProcessing xlog segments for pgm\r\n\t000000010000000000000001\r\n\t000000010000000000000002\r\n\t000000010000000000000003\r\n\t000000010000000000000003.00000020.backup\r\n\t000000010000000000000004\r\nStarting remote restore for server pgm using backup 20160415T165403 \r\nDestination directory: \/var\/lib\/pgsql\/9.2\/data\r\nCopying the base backup.\r\nCopying required wal segments.\r\nThe archive_command was set to 'false' to prevent data losses.\r\n\r\nYour PostgreSQL server has been successfully prepared for recovery!\r\n\r\nPlease review network and archive related settings in the PostgreSQL\r\nconfiguration file before starting the just recovered instance.\r\n\r\nWARNING: Before starting up the recovered PostgreSQL server,\r\nplease review also the settings of the following configuration\r\noptions as they might interfere with your current recovery attempt:\r\n\r\n    external_pid_file = '\/var\/lock\/subsys\/postgresql-9.2'\t\t\t# write an extra PID file\r\n<\/pre>\n<p>Una vez recuperado, simplemente deberemos levantar la instancia:<\/p>\n<pre>\r\n\/etc\/init.d\/postgresql-9.2 start\r\n<\/pre>\n<p>Si nos conectamos, veremos que tenemos los datos en el momento de hacer el backup:<\/p>\n<pre>\r\n[root@pgm ~]# psql -U postgres\r\npsql (9.2.16)\r\nType \"help\" for help.\r\n\r\npostgres=# select * from test;\r\n       txt        \r\n------------------\r\n sakura\r\n enlargeyourpenis\r\n(2 rows)\r\n\r\npostgres=# \r\n<\/pre>\n<p>\tTags: <a href=\"http:\/\/systemadmin.es\/tag\/postgresql\" title=\"postgreSQL\" rel=\"tag\">postgreSQL<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&iacute;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&eacute;n deberemos configurar acceso mediante claves SSH (sin contrase&ntilde;a) en ambas [&hellip;]<\/p>\n","protected":false},"author":321,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5052,824],"tags":[],"class_list":["post-29554","post","type-post","status-publish","format-standard","hentry","category-dba","category-postgresql"],"_links":{"self":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts\/29554","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/users\/321"}],"replies":[{"embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/comments?post=29554"}],"version-history":[{"count":1,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts\/29554\/revisions"}],"predecessor-version":[{"id":29555,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts\/29554\/revisions\/29555"}],"wp:attachment":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/media?parent=29554"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/categories?post=29554"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/tags?post=29554"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}