Postgresql: Corruption: WAL files filled up

As last resort, environment was down and there is no transaction logs backup available for restoration. Resetting the WAL and this will result of losing data.

In this case, the transaction log of 0000000100000039000000A8 was missing.

From postgresql.log

Beginning archiving of 0000000100000039000000A8

0000000100000039000000A8 not found yet in archives, continuing

Last backup time found: 2014-10-24 00:00:01

Last backup path: /var/vmware/vpostgres/9.3-archive/backup/20141024/000001/arclog

grep: write error

ls: write error: Broken pipe

Beginning archiving of 0000000100000039000000A8

0000000100000039000000A8 not found yet in archives, continuing

Last backup time found: 2014-10-24 00:00:01

Last backup path: /var/vmware/vpostgres/9.3-archive/backup/20141024/000001/arclog

grep: write error

ls: write error: Broken pipe

Beginning archiving of 0000000100000039000000A8

0000000100000039000000A8 not found yet in archives, continuing

Last backup time found: 2014-10-24 00:00:01

Last backup path: /var/vmware/vpostgres/9.3-archive/backup/20141024/000001/arclog

grep: write error

From vpxd.log

2015-01-21 14:48:01.967 UTC,,,42343,,54bfbc21.a567,2,,2015-01-21 14:48:01 UTC,,0,LOG,00000,"invalid primary checkpoint record",,,,,,,,,""

2015-01-21 14:48:01.977 UTC,,,42343,,54bfbc21.a567,3,,2015-01-21 14:48:01 UTC,,0,LOG,00000,"invalid secondary checkpoint record",,,,,,,,,""

2015-01-21 14:48:01.977 UTC,,,42343,,54bfbc21.a567,4,,2015-01-21 14:48:01 UTC,,0,PANIC,XX000,"could not locate a valid checkpoint record",,,,,,,,,""

Stop all application services the reset the WAL.

sudo -u postgres /opt/vmware/vpostgres/current/bin/pg_resetxlog /var/vmware/vpostgres/current/pgdata/

There will be some data losses but the situation is better than database not coming back up. This typically happened after disk filed up.

Postgres: vacuum specific tables

VACUUM FULL can be intense running on production environment. However, majority of the problem I have heard of are database keeps growing. So, I would one address the space issue without jeopardizing the production environment too much ? Int his case, I know there are a few times that constantly get flush and fill. I know that the following 2 tables always problematic even in Oracle or SQL Server.

VCDB-# \d vpx_event; \d vpx_event_arg
                     Table "vpx.vpx_event"
        Column        |            Type             | Modifiers
----------------------+-----------------------------+-----------
 event_id             | integer                     | not null
 chain_id             | integer                     |
 event_type           | character varying(255)      |
 extended_class       | numeric(1,0)                |
 create_time          | timestamp without time zone |
 username             | character varying(255)      |
 category             | character varying(255)      |
 vm_id                | integer                     |
 vm_name              | character varying(255)      |
 host_id              | integer                     |
 host_name            | character varying(255)      |
 computeresource_id   | integer                     |
 computeresource_type | integer                     |
 computeresource_name | character varying(255)      |
 datacenter_id        | integer                     |
 datacenter_name      | character varying(255)      |
 datastore_id         | integer                     |
 datastore_name       | character varying(255)      |
 network_id           | integer                     |
 network_name         | character varying(255)      |
 network_type         | character(1)                |
 dvs_id               | integer                     |
 dvs_name             | character varying(255)      |
 storagepod_id        | integer                     |
 storagepod_name      | character varying(255)      |
 change_tag_id        | integer                     |
Indexes:
    "pk_vpx_event" PRIMARY KEY, btree (event_id)
    "vpx_event_f1" btree (computeresource_type)
    "vpxi_change_tag" btree (change_tag_id)
    "vpxi_event_category" btree (category)
    "vpxi_event_chain_id" btree (chain_id)
    "vpxi_event_computeresource_id" btree (computeresource_id)
    "vpxi_event_create_time" btree (create_time)
    "vpxi_event_datacenter_id" btree (datacenter_id)
    "vpxi_event_datastore_id" btree (datastore_id)
    "vpxi_event_dvs_id" btree (dvs_id)
    "vpxi_event_event_type" btree (event_type)
    "vpxi_event_extended_class" btree (extended_class)
    "vpxi_event_host_id" btree (host_id)
    "vpxi_event_network_id" btree (network_id)
    "vpxi_event_username" btree (username)
    "vpxi_event_vm_id" btree (vm_id)
Foreign-key constraints:
    "fk_vpx_change_tag" FOREIGN KEY (change_tag_id) REFERENCES vpx_change_tag(change_tag_id) ON DELETE SET NULL
    "fk_vpx_event_ref_computeres" FOREIGN KEY (computeresource_type) REFERENCES vpx_object_type(id)
Referenced by:
    TABLE "vpx_event_arg" CONSTRAINT "fk_vpx_event_arg_ref_event" FOREIGN KEY (event_id) REFERENCES vpx_event(event_id) ON DELETE CASCADE
    TABLE "vpx_entity_last_event" CONSTRAINT "fk_vpx_last_event_event" FOREIGN KEY (last_event_id) REFERENCES vpx_event(event_id) ON DELETE CASC
ADE


               Table "vpx.vpx_event_arg"
       Column       |          Type          | Modifiers
--------------------+------------------------+-----------
 event_id           | integer                | not null
 arg_id             | integer                | not null
 arg_type           | character varying(255) |
 arg_data           | text                   |
 obj_type           | integer                |
 obj_name           | character varying(255) |
 vm_id              | integer                |
 host_id            | integer                |
 computeresource_id | integer                |
 datastore_id       | integer                |
 network_id         | integer                |
 network_type       | character(1)           |
 datacenter_id      | integer                |
 resourcepool_id    | integer                |
 dvs_id             | integer                |
 folder_id          | integer                |
 alarm_id           | integer                |
 scheduledtask_id   | integer                |
Indexes:
    "pk_vpx_event_arg" PRIMARY KEY, btree (event_id, arg_id)
    "vpx_event_arg_f1" btree (obj_type)
    "vpx_event_arg_f2" btree (alarm_id)
    "vpx_event_arg_f3" btree (scheduledtask_id)
    "vpxi_event_arg_event_id" btree (event_id)
Foreign-key constraints:
    "fk_vpx_event_arg_ref_entity" FOREIGN KEY (obj_type) REFERENCES vpx_object_type(id)
    "fk_vpx_event_arg_ref_event" FOREIGN KEY (event_id) REFERENCES vpx_event(event_id) ON DELETE CASCADE


VCDB=# vacuum FULL ANALYZE VERBOSE vpx_event;
INFO:  vacuuming "vpx.vpx_event"
INFO:  "vpx_event": found 0 removable, 284990 nonremovable row versions in 6003 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.21s/0.70u sec elapsed 1.83 sec.
INFO:  analyzing "vpx.vpx_event"
INFO:  "vpx_event": scanned 6003 of 6003 pages, containing 284990 live rows and 0 dead rows; 30000 rows in sample, 284990 estimated total rows
VACUUM
VCDB=# vacuum FULL ANALYZE VERBOSE vpx_event_arg;
INFO:  vacuuming "vpx.vpx_event_arg"
INFO:  "vpx_event_arg": found 0 removable, 1289100 nonremovable row versions in 18360 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.44s/2.14u sec elapsed 5.92 sec.
INFO:  analyzing "vpx.vpx_event_arg"
INFO:  "vpx_event_arg": scanned 18360 of 18360 pages, containing 1289100 live rows and 0 dead rows; 30000 rows in sample, 1289100 estimated total rows
VACUUM
VCDB=#

By vacuum-ing only 2 of these tables, I would have to touch every single tables in the database.

Postgres: Installing Postgres in RHel 6.4.

Install from the following link.

http://www.postgresql.org/download/linux/redhat/

My VMs specification

Red Hat 6.4

Linux postgres 2.6.32-504.3.3.el6.x86_64 #1 SMP Fri Dec 12 16:05:43 EST 2014 x86_64 x86_64 x86_64 GNU/Linux

Requirements

I will make this as requirements as I have learned them the hard way.

Update the OS to the latest especially YUM. This will save us a lot of dependencies problems later on. Took me 2 days to sort out dependencies issues that not even related to Postgres itself and later found out that, once the RHel is up to date, I have much lesser dependencies problem and 

Make sure the gateway and DNS set appropriately. You must make sure  you can ping both postgres.com and redhat.com successfully. Libraries will be gathered and compiled from both sites. Make sure you have RedHat subscription validated as well. Without those appropriate subscription licenses, some libraries will fail especially "uuid.x86_64 0:1.6.1-10.el6" which implicate "postgresql93-contrib" install will fail as well. The "contrib" contains pg_ctl which is essential in starting the postgres and everything else.

The Installation Part

[root@postgres Downloads]# yum update 

Loaded plugins: product-id, refresh-packagekit, rhnplugin, security, subscription-manager There was an error communicating with RHN. RHN Satellite or RHN Classic support will be disabled. Error communicating with server. 

The message was: 
Temporary failure in name resolution Setting up Update Process http://yum.postgresql.org/9.3/redhat/rhel-6Server-x86_64/repodata/repomd.xml: Errno 14] PYCURL ERROR 6 - "Couldn't resolve host 'yum.postgresql.org'" 

Trying other mirror. Error: Cannot retrieve repository metadata (repomd.xml) for repository: pgdg93. Please verify its path and try again [root@postgres Downloads]# ping www.redhat.com 
You have new mail in /var/spool/mail/root

Fixed the Gateway and DNS IP addresses. Ping is working fine.

[root@postgres ~]# ping redhat.com PING redhat.com (209.132.183.105) 56(84) bytes of data. 64 bytes from redirect.redhat.com (209.132.183.105): icmp_seq=1 ttl=227 time=95.9 ms ^C --- redhat.com ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 970ms rtt min/avg/max/mdev = 95.913/95.913/95.913/0.000 ms 

[root@postgres ~]# yum update 
Loaded plugins: product-id, refresh-packagekit, rhnplugin, security, subscription-manager This system is receiving updates from RHN Classic or RHN Satellite. Setting up Update Process 

pgdg93                       | 3.7 kB     00:00 
pgdg93/primary_db            | 126 kB     00:01 
pgdg94                       | 3.6 kB     00:00 
pgdg94/primary_db            |  84 kB     00:00 
rhel-x86_64-server6          | 1.8 kB     00:00 
rhel-x86_64-server6/primary  |  20 MB     02:17 
rhel-x86_64-server-6          14459/14459 

No Packages marked for Update 
[root@postgres ~]# yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm


[root@postgres ~]# yum install postgresql93-server postgresql93-contrib 

Loaded plugins: product-id, refresh-packagekit, rhnplugin, security, subscription-manager This system is receiving updates from RHN Classic or RHN Satellite. 
Setting up Install Process Resolving Dependencies 
--> Running transaction check 
---> Package postgresql93-contrib.x86_64 0:9.3.5-1PGDG.rhel6 will be installed 
--> Processing Dependency: postgresql93 = 9.3.5 for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64 
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64 
---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 will be installed 
--> Running transaction check 
---> Package postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 will be installed 
--> Processing Dependency: postgresql93-libs = 9.3.5-1PGDG.rhel6 for package: postgresql93-9.3.5-1PGDG.rhel6.x86_64 
---> Package uuid.x86_64 0:1.6.1-10.el6 will be installed 
--> Running transaction check 
---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6 will be installed 
--> Finished Dependency Resolution





Dependencies Resolved

======================================================================================================
 Package                       Arch       Version              Repository                 Size
======================================================================================================
Installing:
 postgresql93-contrib         x86_64     9.3.5-1PGDG.rhel6     pgdg93                     485 k
 postgresql93-server          x86_64     9.3.5-1PGDG.rhel6     pgdg93                     4.1 M
Installing for dependencies:
 postgresql93                 x86_64     9.3.5-1PGDG.rhel6     pgdg93                     1.0 M
 postgresql93-libs            x86_64     9.3.5-1PGDG.rhel6     pgdg93                     191 k
 uuid                         x86_64     1.6.1-10.el6          rhel-x86_64-server-6        54 k

Transaction Summary
=======================================================================================================
Install       5 Package(s)

Downloading Packages:
(1/5): postgresql93-9.3.5-1PGDG.rhel6.x86_64.rpm                | 1.0 MB     00:07
(2/5): postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64.rpm        | 485 kB     00:03
(3/5): postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64.rpm           | 191 kB     00:02
(4/5): postgresql93-server-9.3.5-1PGDG.rhel6.x86_64.rpm         | 4.1 MB     00:27
(5/5): uuid-1.6.1-10.el6.x86_64.rpm                             |  54 kB     00:00
---------------------------------------------------------------------------------------------------
Total                                                  101 kB/s | 5.8 MB     00:58

Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64         1/5
  Installing : postgresql93-9.3.5-1PGDG.rhel6.x86_64              2/5
  Installing : uuid-1.6.1-10.el6.x86_64                           3/5
  Installing : postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64      4/5
  Installing : postgresql93-server-9.3.5-1PGDG.rhel6.x86_64       5/5
  Verifying  : postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64      1/5
  Verifying  : postgresql93-9.3.5-1PGDG.rhel6.x86_64              2/5
  Verifying  : postgresql93-server-9.3.5-1PGDG.rhel6.x86_64       3/5
  Verifying  : postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64         4/5
  Verifying  : uuid-1.6.1-10.el6.x86_64                           5/5

Installed:

Installed:
  postgresql93-contrib.x86_64 0:9.3.5-1PGDG.rhel6 
  postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6

  Dependency Installed:
  postgresql93.x86_64 0:9.3.5-1PGDG.rhel6
  postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6
  uuid.x86_64 0:1.6.1-10.el6


Complete!

Set the environment so postgres starts when the VM is started.

[root@postgres ~]# service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
[root@postgres ~]# chkconfig postgresql-9.3 on
[root@postgres ~]#

-bash-4.1$  ps -ef|grep postgres|grep -v grep
avahi     1892     1  0 13:08 ?        00:00:01 avahi-daemon: running [postgres.local]
root      5051  4433  0 14:48 pts/2    00:00:00 su - postgres
postgres  5053  5051  0 14:48 pts/2    00:00:00 -bash
postgres  5127     1  0 14:55 pts/2    00:00:00 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data
postgres  5129  5127  0 14:55 ?        00:00:00 postgres: logger process
postgres  5131  5127  0 14:55 ?        00:00:00 postgres: checkpointer process
postgres  5132  5127  0 14:55 ?        00:00:00 postgres: writer process
postgres  5133  5127  0 14:55 ?        00:00:00 postgres: wal writer process
postgres  5134  5127  0 14:55 ?        00:00:00 postgres: autovacuum launcher process
postgres  5135  5127  0 14:55 ?        00:00:00 postgres: stats collector process

postgres  5722  5053  2 17:36 pts/2    00:00:00 ps -ef

-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

(1 row)

Other problems I encountered before this successful attempt.

Problem #1

[root@postgres ~]# yum install postgresql93-server postgresql93-contrib
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
Updating certificate-based repositories.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93-contrib.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: postgresql93 = 9.3.5 for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-9.3.5-1PGDG.rhel6.x86_64
---> Package postgresql93-contrib.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64
---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libossp-uuid.so.16()(64bit)
Error: Package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libssl.so.10(libssl.so.10)(64bit)
Error: Package: postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
Error: Package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
Error: Package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libssl.so.10(libssl.so.10)(64bit)
Error: Package: postgresql93-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libssl.so.10(libssl.so.10)(64bit)
Error: Package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
Error: Package: postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libssl.so.10(libssl.so.10)(64bit)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest
[root@postgres ~]# yum install --skip-broken postgresql93-server postgresql93-contrib

My RHel system was outdated including the YUM. I updated the OS with System Update Manager and led to second problem below.

Problem # 2

[root@postgres ~]# yum install postgresql93-server postgresql93-contrib
Loaded plugins: product-id, refresh-packagekit, rhnplugin, security, subscription-manager
There was an error communicating with RHN.
RHN Satellite or RHN Classic support will be disabled.
Error communicating with server. The message was:
Temporary failure in name resolution
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93-contrib.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: postgresql93 = 9.3.5 for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
---> Package postgresql93-contrib.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64
---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
--> Finished Dependency Resolution
Error: Package: postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64 (pgdg93)
           Requires: libossp-uuid.so.16()(64bit)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

The reason for this dependency problem is that my DNS and gateway were not set correctly and it does not connect to redhat portal to gather the library. I have tried to download the rpm from other sites and were never worked right. Once my DNS and Gateway were fixed, the installation was smooth. So, bottom line is, check your network if you are getting this.


Once the network part was fixed, the installation went smoothly.