Postgresql installation and upgrade to a new version

Check the Current Postgresql version

root@ubuntu:/home/sophal# su postgres
postgres@ubuntu:/home/sophal$ psql --version
psql (PostgreSQL) 8.4.11
contains support for command-line editing

Backup all the data

root@ubuntu:/home/sophal# su postgres
postgres@ubuntu:/home$ pg_dumpall > /tmp/backup_postgresql_db

Check backup file

Check the backup file and save the backup file to a storage.

postgres@ubuntu:/home$ ls /tmp
backup_postgresql_db  orbit-sophal	  pulse-PKdhtXMmr18n	 VMwareDnD
keyring-PWYBsI	      pulse-2L9K88eMlGn7  ssh-xXVQRPuB1623	 vmware-root
orbit-gdm	      pulse-9xd9sKlmzYhX  virtual-sophal.Wug6EG  vmware-sophal

Remove the current Postgresql

root@ubuntu:/home# apt-get purge postgresql-8.4

Download and Install

It will open a gui for you.

root@ubuntu:/home/sophal/Downloads# chmod a+x postgresql-9.1.4-1-linux-x64.run
root@ubuntu:/home/sophal/Downloads# ./postgresql-9.1.4-1-linux-x64.run 

Check Process

root@ubuntu:/opt/PostgreSQL/9.1/bin# ps auxw | grep post
postgres  3496  0.0  0.0  55200  1592 pts/0    S    14:26   0:00 su postgres
postgres  3504  0.0  0.0  24976  2400 pts/0    S    14:26   0:00 bash
postgres  3681  0.0  0.0  55200  1592 pts/1    S    14:29   0:00 su postgres
postgres  3689  0.0  0.0  24976  2360 pts/1    S    14:29   0:00 bash
postgres  5754  0.0  0.0  55200  1592 pts/0    S    15:14   0:00 su postgres
postgres  5762  0.0  0.0  24984  2420 pts/0    S+   15:14   0:00 bash
postgres  5805  0.0  0.1  38092  4348 pts/0    T    15:18   0:00 vim 9.1
postgres  5814  0.0  0.1  37584  3612 pts/0    T    15:19   0:00 vim pg_env.sh
postgres  6157  0.0  0.0  55200  1592 pts/1    S    15:34   0:00 su postgres
postgres  6165  0.0  0.0  24984  2368 pts/1    S+   15:34   0:00 bash
root      6390  0.0  0.1  37636  3652 pts/2    T    15:45   0:00 vim postgresql.conf
root      6501  0.0  0.1  37620  3572 pts/2    T    15:52   0:00 vim postgresql.conf
postgres  6850  0.0  0.2  93004  8160 pts/2    S    16:09   0:00 /opt/PostgreSQL/9.1/bin/postgres 
-D /opt/PostgreSQL/9.1/data
postgres  6851  0.0  0.0  59152  1032 ?        Ss   16:09   0:00 postgres: logger process                                    
postgres  6853  0.0  0.0  93004  1368 ?        Ss   16:09   0:00 postgres: writer process                                    
postgres  6854  0.0  0.0  93004  1360 ?        Ss   16:09   0:00 postgres: wal writer process                                
postgres  6855  0.0  0.0  93872  2656 ?        Ss   16:09   0:00 postgres: autovacuum launcher process                       
postgres  6856  0.0  0.0  61488  1476 ?        Ss   16:09   0:00 postgres: stats collector 
process                           
root      6863  0.0  0.0  77124  2868 pts/2    T    16:09   0:00 /usr/lib/postgresql/8.4/bin/psql 
template1
postgres  6872  0.0  0.0  55200  1592 pts/2    S    16:10   0:00 su postgres
postgres  6880  0.0  0.0  24976  2352 pts/2    S+   16:10   0:00 bash
root      6926  0.0  0.0  13132  1048 pts/3    S+   16:12   0:00 grep --color=auto post

Modify the postgresql.conf file

Error

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"?

Solution 1

• You need to modify the postgresql.conf file.
• location of the file: /opt/PostgreSQL/9.1/data

unix_socket_directory = '/tmp'

Change it to:

unix_socket_directory = '/var/run/postgresql'

Solution 2

postgres@ubuntu:/opt/PostgreSQL/9.1/bin$ ./pg_ctl -D /opt/PostgreSQL/9.1/data start

restart the server

postgres@ubuntu:/opt/PostgreSQL/9.1/data$ /etc/init.d/postgresql-9.1 restart
Restarting PostgreSQL 9.1: 
Password: 
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
PostgreSQL 9.1 restarted successfully

Restore the backup file and you done

root@ubuntu:/home/sophal/Downloads# su postgres
postgres@ubuntu:/home/sophal/Downloads$ psql < /tmp/backup_postgresql_db