Thursday, September 4, 2014

Transfer user grants from one Postgresql instance to another

0 comments
Transferring role from one host to another:

pg_dumpall -r -h source_host |> roles. sql
psql -h destination_host -f roles.sql

Transfer role grants from one host to another

pg_dump -h source_host -s | egrep '(GRANT|REVOKE)' > grants,sql
psql -h destination_host -f grants.sql 
Continue reading →
Friday, July 25, 2014

Postgresql single mode and recover from transaction wrap around

1 comments
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.
http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html
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/libz.so.1: version `ZLIB_1.2.3.3' not found (required by /opt/msp/pkg/postgres/bin/../lib/libxml2.so.2)

I saw that libz.so.1 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.
Usage:
  postgres [OPTION]...
Options:
  -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 →
Monday, February 10, 2014

Understanding XID Wrap around

0 comments

A few days back, I faced a disaster with one of the databases with xid wrap around issue. I started to dig into the details about the actual case for the failure. There are some very good articles about xid mechanism. I added those as references below.

However, I added my test result here.

Created a brand new cluster:

initdb
   
postgres=# select datname, datfrozenxid from pg_database;
  datname  | datfrozenxid
-----------+--------------
 template1 |         1798
 template0 |         1798
 postgres  |         1798
(3 rows)
   

postgres=# select txid_current();
 txid_current
--------------
         1809
(1 row)

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres92".
testdb=# create table t ( i int);
CREATE TABLE
testdb=# insert into t values (1);
INSERT 0 1
testdb=# select txid_current();
 txid_current
--------------
         1812
(1 row)


testdb=# select datname, datfrozenxid from pg_database;
  datname  | datfrozenxid
-----------+--------------
 template1 |         1798
 template0 |         1798
 postgres  |         1798
 testdb    |         1798
(4 rows)


testdb=# select txid_current();
 txid_current
--------------
         1854
(1 row)


Identified in the source:


In postgresql-9.2.4/src/backend/access/varsup.c,
I found functions:
 TransactionId GetNewTransactionId(bool isSubXact)
 TransactionId ReadNewTransactionId(void)
 void7 SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)

After every start of the database and after every execution of autovacuum process, it calls "SetTransactionIdLimit" to set the transaction xid limit.
It follows the following formula:

xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age;
xidWarnLimit = xidStopLimit - 10000000; // (10M)
xidStopLimit = xidWrapLimit - 1000000; //(1M)
xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);

When db xid crosses xidVacLimit, it startes autovacuum process.
When db xid crosses xidWarnLimit, it warns us.
When db xid crosses xidStopLimit, it stops to generate any new xid with alerts.



In our case:


oldest_datfrozenxid = 1798
MaxTransactionId = 4294967295

xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
MaxTransactionId >> 1 = 2147483647
xidWrapLimit = 2147485445

xidStopLimit = xidWrapLimit - 1000000;  (1M)
xidStopLimit = 2146485445


// We'll start complaining loudly when we get within 10M transactions of the stop point.
xidWarnLimit = xidStopLimit - 10000000; (10M)
xidWarnLimit = 2136485445

//We'll start trying to force autovacuums when oldest_datfrozenxid gets to be more than autovacuum_freeze_max_age transactions old.
xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age;
xidVacLimit = 1798 + 100000000 (100M)


So, I modfieid the values as following to get the alerts:
printf("\nRumman: SetTransactionId: assign\n")
xidVacLimit = 1900;
xidWarnLimit = 1910;
xidStopLimit = 2000;
xidWrapLimit=2500;

So when I started the db, I get the followings in log:   
  
Rumman: SetTransactionId: assign

Rumman: SetTransactionID() : xidVacLimit = 1900
Rumman: SetTransactionID() : xidWarnLimit = 1910
Rumman: SetTransactionID() : xidStopLimit = 2000
Rumman: SetTransactionID() : xidWrapLimit = 2500
Rumman: SetTransactionId: curXid = 1903
Rumman: SetTransactionID: passed xidVacLimit

Then I increased the txid using txid_current() and crossed the value 1910 and got:
Rumman: getTransactionId: passed vacuum limit
WARNING:  database "testdb" must be vacuumed within 590 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
    You might also need to commit or roll back old prepared transactions.
WARNING:  database "testdb" must be vacuumed within 589 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
    You might also need to commit or roll back old prepared transactions.
WARNING:  database "postgres" must be vacuumed within 588 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
    You might also need to commit or roll back old prepared transactions.


No, I modfied the values for xidStopLimit and xidWrapLimit to maximum and run the db:
Rumman: getTransactionId: passed vacuum limit

Rumman: SetTransactionId: assign

Rumman: SetTransactionID() : xidVacLimit = 1900
Rumman: SetTransactionID() : xidWarnLimit = 1910

Rumman: SetTransactionId: curXid = 1912
Rumman: SetTransactionID: passed xidVacLimit

Rumman: Set TransactionID (): passed xidWarnLimit
In psql console:

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres92".
testdb=# select datname, datfrozenxid from pg_database;
  datname  | datfrozenxid
-----------+--------------
 template1 |         1798
 template0 |         1798
 postgres  |         1798
 testdb    |         1798
(4 rows)


No exeucted Vacuum Full and the xid changed:
VACUUM FULL;

testdb=# select datname, datfrozenxid from pg_database;
  datname  | datfrozenxid
-----------+--------------
 template1 |         1798
 template0 |         1798
 postgres  |         1798
 testdb    |         1905
(4 rows)

testdb=# insert into t values (1);
ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and use a standalone backend to vacuum that database.
You might also need to commit or roll back old prepared transactions.

Then, modified the source of varsup.c with its original and run the db and it worked fine.

So, I got into the result:
xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age
We should always take care of 
"datfrozenxid + autovacuum_freeze_max_age" 
in order to prevent xid wrap around issue;
Here,
datfrozenxid - is the column of a database's pg_database row is a lower bound on the normal XIDs appearing in that database — it is just the minimum of the per-table relfrozenxid values within the database.

And relfrozenxid is the column of a table's pg_class row contains the freeze cutoff XID that was used by the last whole-table VACUUM for that table.  All normal XIDs older than this cutoff XID are guaranteed to have been replaced by FrozenXID within the table.

References:

http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html



http://www.databasesoup.com/2012/10/freezing-your-tuples-off-part-2.html


Continue reading →
Wednesday, January 22, 2014

Exclude tables during pg_restore

30 comments
Let we have a database like:

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+----------
 public | t    | table | postgres
 public | t1   | table | postgres
 public | t2   | table | postgres
(3 rows)


testdb=# insert into t1 select c, c::text||'abc' as some_text from generate_series(1,10) as c;
INSERT 0 10
testdb=# insert into t2 select c, c::text||'abc' as some_text from generate_series(1,10) as c;
INSERT 0 10
testdb=# insert into t select c, c::text||'abc' as some_text from generate_series(1,10) as c;
testdb=# insert into t select c, (c::text||'abc')::bytea as some_text from generate_series(1,10) as c;
INSERT 0 10


Take a dump of the database:

pg_dump testdb -Fc -v > testdb.sqlc

Now our task is to restore the database excluding table "t1":

Create a new database:

create database testdb_new;

Restore schema only:

pg_restore -d testdb_new -s -v testdb.sqlc

pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating TABLE t
pg_restore: creating TABLE t1
pg_restore: creating TABLE t2
pg_restore: creating CONSTRAINT t1_pkey
pg_restore: creating CONSTRAINT t_pkey
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for TABLE t
pg_restore: setting owner and privileges for TABLE t1
pg_restore: setting owner and privileges for TABLE t2
pg_restore: setting owner and privileges for CONSTRAINT t1_pkey
pg_restore: setting owner and privileges for CONSTRAINT t_pkey

Drop table "t2":

postgres=# \c testdb_new
You are now connected to database "testdb_new" as user "postgres".
testdb_new=# drop table  t1;
DROP TABLE

Restore data:

pg_restore -d testdb_new -a -v testdb.sqlc

pg_restore: connecting to database for restore
Password:
pg_restore: restoring data for table "t"
pg_restore: restoring data for table "t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2728; 0 35908 TABLE DATA t1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "t1" does not exist
    Command was: COPY t1 (i, some_text) FROM stdin;

pg_restore: restoring data for table "t2"
pg_restore: setting owner and privileges for TABLE DATA t
pg_restore: setting owner and privileges for TABLE DATA t1
pg_restore: setting owner and privileges for TABLE DATA t2
WARNING: errors ignored on restore: 1

Verify:

testdb_new=# \d
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+----------
 public | t    | table | postgres
 public | t2   | table | postgres
(2 rows)
testdb_new=# select * from t;
 i  |  nam 
----+-------
  1 | 1abc
  2 | 2abc
  3 | 3abc
  4 | 4abc
  5 | 5abc
  6 | 6abc
  7 | 7abc
  8 | 8abc
  9 | 9abc
 10 | 10abc
(10 rows)


Continue reading →
Wednesday, January 15, 2014

Postgresql schema sizes

0 comments
In order to get schema sizes of a large database, I used the following sql:

select schemaname, pg_size_pretty(sum(pg_table_size(schemaname||'.'||relname))::bigint) as s
from pg_stat_user_tables
group by schemaname

   schemaname   |    s  
----------------+---------
 schema1        | 11 GB
 temp           |   1088 kB
 schema2        | 51 GB
 schema3        | 179 GB
(4 rows)


Continue reading →

Labels