Friday, July 25, 2014

Postgresql single mode and recover from transaction wrap around

We got into a problem with the follwoing error:
"FATAL: database is not accepting commands to avoid wraparound data loss in database "testdb""

This means postgresql database went out of its xid.
There is a good document on it why it was happened.
23.1.5. Preventing Transaction ID Wraparound Failures

So to recover from it, I stopped the running postgresql instance and started in single user mode.

~/bin/postgres --single -D /opt/msp/pkg/postgres/data/ testdb

Then started vacuum full on testdb.

backend> vacuum full analyze verbose;

However, I found a problem when I was running postgres command to get into single user mode.

-bash-4.1$ ~/bin/postgres --help
/opt/msp/pkg/postgres/bin/postgres: /lib64/ version `ZLIB_1.2.3.3' not found (required by /opt/msp/pkg/postgres/bin/../lib/

I saw that at /opt/msp/pkg/postgres/bin/../lib and /lib64/ and it was creating the problem.

I used LD_LIRBARY_PATH to /opt/msp/pkg/postgres/bin/../lib.
export LD_LIRBARY_PATH=/opt/msp/pkg/postgres/bin/../lib

Now it works:
~/bin/postgres --help
postgres is the PostgreSQL server.
  postgres [OPTION]...
  -B NBUFFERS     number of shared buffers
  -c NAME=VALUE   set run-time parameter
  -d 1-5          debugging level
  -D DATADIR      database directory
  -e              use European date input format (DMY)
  -F              turn fsync off
  -h HOSTNAME     host name or IP address to listen on
  -i              enable TCP/IP connections
  -k DIRECTORY    Unix-domain socket location
  -l              enable SSL connections
  -N MAX-CONNECT  maximum number of allowed connections
  -o OPTIONS      pass "OPTIONS" to each server process (obsolete)
  -p PORT         port number to listen on
  -s              show statistics after each query
  -S WORK-MEM     set amount of memory for sorts (in kB)
  --NAME=VALUE    set run-time parameter
  --describe-config  describe configuration parameters, then exit
  --help          show this help, then exit
  --version       output version information, then exit
Continue reading →