Adventure with Postgresql on Ubuntu

Setting postgresql to not start during startup:

To enable or disable postgresql start at machine startup edit start.conf located @ /etc/postgresql/9.2/main/ folder and add proper configuration auto |  manual | disabled 

$sudo vi /etc/postgresql/9.2/main/start.conf

# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
# pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
# circumvented and is only meant to be a small protection for
# accidents).

auto

postgres database server Startup or Shutdown:

postgresql database server can start directly as service from services tool i.e. “sudo services postgresql start|stop|*” or using a wrapper named pg_ctl, as below: 

$sudo services postgresql start

$ /usr/lib/postgresql/9.2/bin/pg_ctl 

If postgresql is start through wrapper with specific catalog, then the status display different results as below:   

$ sudo service postgresql status
9.2/main (port 5432): down

$ /usr/lib/postgresql/9.2/bin/pg_ctl status -D /home/hadoopuser/hadoopuser_DATA/pgdb -l /home/hadoopuser/hadoopuser_DATA/logs/pg.log -w 

pg_ctl: server is running (PID: 3381)
/usr/lib/postgresql/9.2/bin/postgres “-D” “/home/hadoopuser/hadoopuser_DATA/pgdb”
hadoopuser@HADOOP_CLUSTER:~$

Handling portgresql error “could not create lock file “/var/run/postgresql/.s.PGSQL.5432.lock”: Permission denied”

Sometimes starting postgresql server returns the following error:

WARNING: could not create listen socket for “*”
FATAL: could not create any TCP/IP sockets
FATAL: could not create lock file “/var/run/postgresql/.s.PGSQL.5432.lock”: Permission denied

The problem is that in ubuntu postgresql is running and during installation hadoopuser is starting it and because of ownership issue hadoopuser can not use it and user see the lock issue as shown in above logs:

 

hadoopuser@hadoopserver:~$ ls -l /var/run/
drwxrwsr-x 2 postgres postgres 40 May 28 22:16 postgresql

hadoopuser@hadoopserver:~$ sudo chmod 777 /var/run/postgresql

hadoopuser@hadoopserver:/usr/local/hadoopuser/current$ ls -l /var/run/
drwxrwsrwx 2 postgres postgres 80 May 28 22:32 postgresql

 

Handling postgresql error “Is another postmaster already running on port 5432”

Sometimes starting postgresql server returns the following error:

HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG: could not bind IPv6 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
WARNING: could not create listen socket for “*”
FATAL: could not create any TCP/IP sockets

To solve this problem please first check if postgresql is running as below:

hadoopuser@hadoopserver:~$ ps auxwww | grep postg
hadoopuser 1805 0.0 0.1 137520 10212 pts/0 S 22:25 0:00 /usr/lib/postgresql/9.2/bin/postgres -D /home/hadoopuser/hadoopuser_DATA/pgdb
hadoopuser 1807 0.0 0.0 137520 1492 ? Ss 22:25 0:00 postgres: checkpointer process
hadoopuser 1808 0.0 0.0 137520 1732 ? Ss 22:25 0:00 postgres: writer process
hadoopuser 1809 0.0 0.0 137520 1492 ? Ss 22:25 0:00 postgres: wal writer process
hadoopuser 1810 0.0 0.0 138300 2776 ? Ss 22:25 0:00 postgres: autovacuum launcher process
hadoopuser 1811 0.0 0.0 97208 1572 ? Ss 22:25 0:00 postgres: stats collector process
hadoopuser 2165 0.0 0.0 8104 924 pts/0 S+ 22:32 0:00 grep –color=auto postg

Stop the postgresql service as below:

hadoopuser@hadoopserver:~$ sudo service postgresql stop
* Stopping PostgreSQL 9.2 database server [ OK ]

Note: Above command does not guarantee to stop services if the database server was started in other user context and actively running with an open database. 

or kill the postgresql process as 

hadoopuser@hadoopserver:~$ killall postg

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s