wraparound data loss in database "postgres"

[QS1]

The following command was run on Friday 14March at 8am and ran all weekend with 4 billion files still remaining.
are there any other options for postgresql (uninstall/re-install?). If so what data would I be losing?

DETAIL: You may have already suffered transaction-wraparound data loss.
WARNING: database "postgres" must be vacuumed within 4288941414 transactions



[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@nagiosxi ~]# su postgres
bash-3.2$ postgres -D /var/lib/pgsql/data/ nagiosxi
WARNING: database "postgres" must be vacuumed within 4288941485 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941485 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
backend> vacuum full;
WARNING: database "postgres" must be vacuumed within 4288941483 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941482 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941481 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941480 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941479 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941478 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941477 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941476 transactions
DETAIL: You may have already suffered transaction-wraparound data loss.
WARNING: database "postgres" must be vacuumed within 4288941414 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
backend>

[lmiltchev]

Let's try this one more time - run the following commands (one at a time), and show the output after each command.

Code: Select all

service postgresql stop
su postgres
postgres -D /var/lib/pgsql/data/
vacuum;
vacuum analyse;
vacuum full;

CTRL+D

[QS1]

[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@nagiosxi ~]# su postgres
bash-3.2$ postgres -D /var/lib/pgsql/data/

WARNING: database "postgres" must be vacuumed within 4288941414 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941414 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".

PostgreSQL stand-alone backend 8.1.22
backend> vacuum;

WARNING: database "postgres" must be vacuumed within 4288941413 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
backend> vacuum analyse;

WARNING: database "postgres" must be vacuumed within 4288941411 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941410 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941409 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941408 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941407 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941406 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941405 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941404 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941403 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941402 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941401 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 4288941400 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "pos
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: some databases have not been vacuumed in over 2 billion transactions
DETAIL: You may have already suffered transaction-wraparound data loss.
WARNING: database "template1" must be vacuumed within 4288941319 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "template1".
backend>vacuum full;

WARNING: database "template1" must be vacuumed within 4288941319 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "template1".
ERROR: canceling statement due to user request
backend> CTRL+D

backend> You have new mail in /var/spool/mail/root
bash-3.2$

[scottwilkerson]

Lets run the following

Code: Select all

echo "vacuum;vacuum analyse;vacuum full;"|psql nagiosxi postgres
echo "vacuum;vacuum analyse;vacuum full;"|psql postgres postgres
echo "vacuum;vacuum analyse;vacuum full;"|psql template1 postgres

[QS1]

bash-3.2$ echo "vacuum;vacuum analyse;vacuum full;"|psql nagiosxi postgres
could not change directory to "/root"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
You have new mail in /var/spool/mail/root
bash-3.2$

bash-3.2$ echo "vacuum;vacuum analyse;vacuum full;"|psql postgres postgres
could not change directory to "/root"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
bash: echo: write error: Broken pipe

bash-3.2$ echo "vacuum;vacuum analyse;vacuum full;"|psql template1 postgres
could not change directory to "/root"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
You have new mail in /var/spool/mail/root
bash-3.2$

[abrist]

Try starting the postgres service, and then rerunning Scotts commands:

Code: Select all

service postgresql start
echo "vacuum;vacuum analyse;vacuum full;"|psql nagiosxi postgres
echo "vacuum;vacuum analyse;vacuum full;"|psql postgres postgres
echo "vacuum;vacuum analyse;vacuum full;"|psql template1 postgres

[QS1]

Starting postgresql service: [ OK ]
[root@nagiosxi ~]# echo "vacuum;vacuum analyse;vacuum full;"|psql nagiosxi postgres
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "template1"
HINT: Stop the postmaster and use a standalone backend to vacuum database "template1".

[root@nagiosxi ~]# echo "vacuum;vacuum analyse;vacuum full;"|psql postgres postgres
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "template1"
HINT: Stop the postmaster and use a standalone backend to vacuum database "template1".

[root@nagiosxi ~]# echo "vacuum;vacuum analyse;vacuum full;"|psql template1 postgres
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "template1"
HINT: Stop the postmaster and use a standalone backend to vacuum database "template1".
[root@nagiosxi ~]#

[abrist]

Alright, lets try one more time with a slightly altered command:

Code: Select all

service postgreql stop
su postgres
postgres --single -D /var/lib/pgsql/data/ -O nagiosxi
vacuum;vacuum analyse;vacuum full;
ctrl-d
exit
service postgresql start

[QS1]

[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
You have new mail in /var/spool/mail/root
[root@nagiosxi ~]# su postgres
bash-3.2$ postgres --single -D /var/lib/pgsql/data/ -O nagiosxi
FATAL: --single requires a value

(tried also with a lower case switch -o)
bash-3.2$ postgres --single -D /var/lib/pgsql/data/ -o nagiosxi
FATAL: --single requires a value

bash-3.2$ vacuum;vacuum analyse;vacuum full;
bash: vacuum: command not found
bash: vacuum: command not found
bash: vacuum: command not found
bash-3.2$

[abrist]

Well, I have run out of syntax options - we really should try this in a remote. As you are a client, I would suggest opening a ticket by emailing [email protected] from an eligible email address and we will setup a remote session to resolve this. Make sure to reference the url to this thread in your email so that I get assigned on it.