Postgres tablespace creation

Create a tablespace in postgresql in two simple steps :

1) Make a tablespace directory
mkdir -p /var/lib/pgsql/tablespaces/<tablespace_name>
cd /var/lib/pgsql/tablespaces/
chmod -R 700 <tablespace_name>

2) Create tablespace
psql test
test=# create tablespace <tablespace_name> location '/var/lib/pgsql/tablespaces/<tablespace_name>';

After creating tablespace we should basically include this in the ddl
SET default_tablespace = <tablespace_name>;
Create table mytable(id integer);

and now table 'mytable' will belong to our newly created tablespace!

Slony - number of records yet to be processed in sl_log tables

Two tables in SLONY - sl_log_1 and sl_log_2 stores the changes which need to be propagated to the subscriber nodes. Slony will try to log switch between both of these tables and truncate each of them once all the changes are propagated to the subscriber node. Sometimes there is a chance that these tables grow very huge because of a big table or large data set sync. You could also notice in the logs that SYNC events are taking long time.

remoteWorkerThread_4: SYNC 8002311133 done in 12.30 seconds

Also you may get this error in slony log in master

NOTICE: Slony-I: could not lock sl_log_1 - sl_log_1 not truncated

Finding number of records yet to be processed by slony is important.

Query to find number of records in sl_log_1 yet to be processed by slony 

select count(*) from sl_log_1 where log_txid>(select split_part(cast(ev_snapshot as text),':',1)::bigint from sl_event where ev_seqno=(select st_last_event from sl_status));

similarly you can find number of records yet to be processed in sl_log_2 using

select count(*) from sl_log_2 where log_txid>(select split_part(cast(ev_snapshot as text),':',1)::bigint from sl_event where ev_seqno=(select st_last_event from sl_status));

Postgres grant select access on all tables in a database

Content moved, please visit my wordpress blog to view this content :)

Postgres processlist and locks

Processlist
select * from pg_stat_activity;

Exclude IDLE processes
select * from pg_stat_activity WHERE current_query not like '<IDLE>%';

Current running queries
select current_query,count(*) from pg_stat_activity WHERE current_query not like '<IDLE>%' group by current_query order by count(*) desc;

Queries holding Exclusive locks
select * from pg_stat_activity where procpid in(select pid from pg_locks where mode='ExclusiveLock');
select pid,count(*) from pg_locks where mode='ExclusiveLock' group by pid order by count(*) desc;

Kill a running query
SELECT pg_cancel_backend(procpid);