postgres on ubuntu

#shaifali@sa:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
shaifali@sa:~$ sudo pg_ctlcluster 9.3 main start
[sudo] password for shaifali:
The PostgreSQL server failed to start. Please check the log output:
2014-10-22 22:45:07 IST LOG:  could not bind IPv4 socket: Address already in use
2014-10-22 22:45:07 IST HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2014-10-22 22:45:07 IST WARNING:  could not create listen socket for "localhost"
2014-10-22 22:45:07 IST FATAL:  could not create any TCP/IP sockets
shaifali@sa:~$ sudo pg_ctlcluster 9.3 main start --port 3333
Unknown option: port
shaifali@sa:~$

shaifali@sa:~$  sudo netstat -lptn|grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      1276/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      1276/postgres

shaifali@sa:~$ sudo -u postgres psql
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

shaifali@sa:~$ sudo -u postgres psql -h localhost
Password:
psql: FATAL:  password authentication failed for user "postgres"
shaifali@sa:~$ ps faux|grep postgres
shaifali  3909  0.0  0.0  15940   924 pts/0    S+   23:02   0:00          |       \_ grep --color=auto postgres
postgres  1276  0.0  0.3 208752 13980 ?        S    21:32   0:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres  1285  0.0  0.0  62336  1192 ?        Ss   21:32   0:00  \_ postgres: logger process
postgres  1288  0.0  0.0 208752  1372 ?        Ss   21:32   0:00  \_ postgres: checkpointer process
postgres  1289  0.0  0.0 208752  2168 ?        Ss   21:32   0:00  \_ postgres: writer process
postgres  1290  0.0  0.0 208752  1372 ?        Ss   21:32   0:00  \_ postgres: wal writer process
postgres  1291  0.0  0.0 209480  2476 ?        Ss   21:32   0:00  \_ postgres: autovacuum launcher process
postgres  1292  0.0  0.0  64428  1320 ?        Ss   21:32   0:00  \_ postgres: stats collector process
shaifali@sa:~$
#Reason why postgresql not running is because there's a rogue installation in /opt that's hogging the port
# To solve it  just change 'port' in /etc/postgresql/9.3/main/postgresql.conf and then do 'sudo pg_ctlcluster 9.3 main start' then 'sudo -u postgres psql -p<portnumber>' to connect to it :)

#After restarting computer
shaifali@sa:~$ sudo -u postgres psql -p 3333
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.3333"?
shaifali@sa:~$ psql -p 3333 -h localhost
psql: could not connect to server: Connection refused
	Is the server running on host "localhost" (127.0.0.1) and accepting
	TCP/IP connections on port 3333?

#Since `pg_lsclusters` is showing main cluster and its status is down, we need to start it, use `sudo pg_ctlcluster <the version> main start` to start server and then `sudo -u postgres psql -p 3333` this will run postresql :)

#Also to remove all /opt/PostgreSQL files from starters file --
system startup files resides in /etc/rc?.d (or in systemd or launchd, if your OS uses that); those directories are part of something called sysVinit, which is how older linux systemss start/stop services at boot; the ones that start with Sxx are run to START things, and the Kxx ones are run to STOP things; they are all symlinks to actual scripts in /etc/init.d

# first `sudo /etc/init.d/postgresql-9.3 stop` that should shut it off. then, rm /etc/rc?.d/???postgresql-9.3