PostgreSQL Process names on Solaris

PostgreSQL Processes are very few and countable like, writer process, wal writer proces,stats collector,autovacuum process,syslogger process,archiver process & daemon postmaster. If replication enabled then there will be wal sender & wal receiver process. In my trainings, I use to show process information by executing "ps -ef | grep postgres", but how could I show the same on Solaris. So, I checked with Solaris Documentation and found its very simple and easy to get the process names as linux.

In PostgreSQL documentaion, its said to use /usr/ucb/ps with -ww options to get process names instead of regular /usr/bin/ps, however most of the information are hidden by /usr/ucb/ps option as well. Lets see how to retrieve complete postgres process names in solaris.

Below are my postgres 9.1 instance processes on Solaris:

bash-3.00$ /usr/ucb/ps -awwx | grep postgres
  7778 ?        S  0:04 /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
  7779 ?        S  0:01 /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
  7780 ?        S  0:00 /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
  7781 ?        S  0:00 /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
  7776 pts/5    S  0:00 /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data

More extended way with pargs:

bash-3.00$  pargs `/usr/ucb/ps -awwx | grep postgres | awk '{print $1}'`
7778:   /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
argv[0]: postgres: writer process  
argv[1]:
argv[2]:

7779:   /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
argv[0]: postgres: wal writer process  
argv[1]:
argv[2]:

7780:   /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
argv[0]: postgres: autovacuum launcher process  
argv[1]:
argv[2]:

7781:   /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
argv[0]: postgres: stats collector process  
argv[1]:
argv[2]:

7776:   /Desktop/postgres/9.1-pgdg/bin/64/postgres -D /Desktop/postgres/9.1-pgdg/data
argv[0]: /Desktop/postgres/9.1-pgdg/bin/64/postgres
argv[1]: -D
argv[2]: /Desktop/postgres/9.1-pgdg/data

7776 is postmaster daemon process.

bash-3.00$ cat /Desktop/postgres/9.1-pgdg/data/postmaster.pid
7776
/Desktop/postgres/9.1-pgdg/data
1339917119
5432
/tmp
localhost
  5432001  50331683

Though it seems simple I believe its worth to know :).

--Raghav

Upgrading Slony-I 2.0.x to latest version 2.1.x

Slony-1 2.1 has very good fixes and new features like adding Bulk tables, improvement on WAIT FOR with Merge Set/Move Set, support for TRUNCATE on replicating tables and many more. Am using Slony-I 2.0.7, so thought of upgrading it to latest version. Upgrading Slony-I is very simple and it can be achievable in few steps. My upgrade procedure assumes there is already Master/Slave setup with Slony 2.0.7.

Backup Plan:
1. Backup the existing slony schema (_slonyschema) of master/slave
2. Backup the OLD Slony Binaries
3. Backup all initially creates slony configuration files.

Upgrade Procedure:
1. Stop all running slon proces on all nodes.
2. Install new Version of Slony 2.1.x binaries.
3. Execute SLONIK upgradation script
4. Start slony with new binaries on all nodes.

Link: http://slony.info/documentation/2.1/slonyupgrade.html

Current PostgreSQL & Slony version:

repdb=# select substr(version(),1,26) as "PostgreSQL-Version",_myrep.slonyversion();
     PostgreSQL-Version     | slonyversion
----------------------------+--------------
 PostgreSQL 9.1.3 on x86_64 | 2.0.7
(1 row)

Install/Configure Latest version of Slony-I 2.1.x source

 wget http://main.slony.info/downloads/2.0/source/slony1-2.1.0.tar.bz2
 ./configure --prefix=/opt/PostgreSQL/9.1/bin --with-pgconfigdir=/opt/PostgreSQL/9.1/bin
 make
 make install

After installation, you can find three executables slon, slonik & slon_logshipper under "/opt/PostgreSQL/9.1/bin/bin".  

-bash-4.1$ ./slon -v
slon version 2.1.0

Upgradation Script:

## Upgrade script

cluster name = myrep;
node 1 admin conninfo='host=localhost dbname=postgres user=postgres port=5432';
node 2 admin conninfo='host=localhost dbname=repdb user=postgres port=5433';
UPDATE FUNCTIONS (  ID = 1 );
UPDATE FUNCTIONS (  ID = 2 );

Note: Update all the nodes with UPDATE FUNCTIONS. I have two nodes Master(5432) and Slave(5433). 

Execute the script:

-bash-4.1$ slonik upgrade_207_201.slonik

Start the slony process with new binaries and check for the changes.

postgres=# select substr(version(),1,26) as "PostgreSQL-Version",_myrep.slonyversion();
     PostgreSQL-Version     | slonyversion
----------------------------+--------------
 PostgreSQL 9.1.3 on x86_64 | 2.1.0
(1 row)

You can see my slony version has been upgraded to latest. You can also perform health check on the schema with a function provided by Slony-I in their documenation. Health Check function should return TRUE, else somewhere your PG & Slony catalogs are damaged.
Function link: http://slony.info/documentation/2.1/function.slon-node-health-check.html

postgres=# select node_health_check();
 node_health_check
-------------------
 t
(1 row)

--Raghav

Compiling PL/Proxy with PostgresPlus Advance Server 9.1

PostgresPlus Advance Server 9.1(PPAS) is EnterpriseDB product, which comes with enterprise features as additional with community PostgreSQL. Most of the contrib modules(pgfoundry) can be pluged into this product using Stackbuilder. However,currently Pl/Proxy is not bundled or downloadable with Stack-builder. So,here is how you can compile the Pl/Proxy with PPAS 9.1.

1. Download Pl/Proxy.

wget http://pgfoundry.org/frs/download.php/3274/plproxy-2.4.tar.gz
tar -xvf plproxy-2.4.tar.gz
make PG_CONFIG=/opt/PostgresPlus/9.1AS/bin/pg_config
make intall PG_CONFIG=/opt/PostgresPlus/9.1AS/bin/pg_config

Note: Flex & Bison must be installed before compiling pl/proxy.

2. After sucessfull configuration, you get two files, plproxy.so in $PGPATH/lib & plproxy--2.4.0.sql in $PGPATH/share/extention/ location.
Execute the .sql file which creates call_handler & language.

bash-4.1$ psql -p 5444 -U enterprisedb -d edb -f /opt/PostgresPlus/9.1AS/share/extension/plproxy--2.4.0.sql
CREATE FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
CREATE FOREIGN DATA WRAPPER

Now you can see the language installed.

edb=# \dL
        List of languages
  Name   |    Owner     | Trusted
---------+--------------+---------
 edbspl  | enterprisedb | t
 plpgsql | enterprisedb | t
 plproxy | enterprisedb | f
(3 rows)

3. Lets test the sample code with pl/proxy.

create table users(username text,blog text);
insert into users values('Raghav','raghavt.blogspot.com');

CREATE or replace  FUNCTION get_user_blog(i_username text)
RETURNS SETOF text AS $$
    CONNECT 'dbname=edb';
    SELECT blog FROM users WHERE username = $1;
$$ LANGUAGE plproxy;

edb=# select * from get_user_blog('Raghav');
          get_user_blog
----------------------------------
 raghavt.blogspot.com
(1 rows)

All set to go testing with pl/proxy on PPAS 9.1. If you want to know how to setup pl/proxy, follow below links.
http://www.depesz.com/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/
http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/

--Raghav