Introduction
PostgreSQL is a powerful, open-source object-relational database system. It runs under all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS, Solaris, Tru64), and Windows OS.
PostgreSQL 9.4 has been released last week with major enhancements, fixes, and features. Read what is new in PostgreSQL 9.4 here.
In this handy tutorial, let us see how to install PostgreSQL 9.4 on Ubuntu 14.10 server.
Install PostgreSQL
PostgreSQL is available in the default repositories. So enter the following command from the Terminal to install it.
sudo apt-get install postgresql postgresql-contrib
If you’re looking for other versions, add the PostgreSQL repository, and install it as shown below.
On Ubuntu 14.10 systems:
Create the file /etc/apt/sources.list.d/pgdg.list;
vi /etc/apt/sources.list.d/pgdg.list
Add a line for the repository:
deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main
On Ubuntu 14.04, add the following line:
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
On Ubuntu 12.04, add the following line:
deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
‘*’‘*’‘*’Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc sudo apt-key add -
Update the package lists:
sudo apt-get update
Then install the required version.
sudo apt-get install postgresql-9.4
Access PostgreSQL command prompt
The default database name and database user are “postgres”. Switch to postgres user to perform postgresql related operations:
sudo -u postgres psql postgres
Sample Output:
psql (9.4rc1) Type "help" for help. postgres=#
To exit from posgresql prompt, type \q in the psql prompt return back to the Terminal.
Set “postgres” user password
Login to postgresql prompt,
sudo -u postgres psql postgres
.. and set postgres password with following command:
postgres=# \password postgres Enter new password: Enter it again: postgres=# \q
To install PostgreSQL Adminpack, enter the command in postgresql prompt:
postgres=# CREATE EXTENSION adminpack; CREATE EXTENSION
Create New User and Database
For example, let us create a new user called “senthil” with password “ubuntu”, and database called “mydb”.
sudo -u postgres createuser -D -A -P senthil‘*’‘*’ sudo -u postgres createdb -O senthil mydb
Delete Users and Databases
To delete the database, switch to postgres user:
sudo -u postgres psql postgres
Enter command:
$ drop database <database-name>
To delete a user, enter the following command:
$ drop user <user-name>
Configure PostgreSQL-MD5 Authentication
MD5 authentication requires the client to supply an MD5-encrypted password for authentication. To do that, edit /etc/postgresql/9.4/main/pg_hba.conf file:
sudo vi /etc/postgresql/9.4/main/pg_hba.conf
Add or Modify the lines as shown below
[...] # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.1.0/24 md5 # IPv6 local connections: host all all ::1/128 md5 [...]
Restart postgresql service to apply the changes:
sudo service postgresql restart
Configure PostgreSQL-Configure TCP/IP
By default, TCP/IP connection is disabled, so that the users from another computers can’t access postgresql. To allow to connect users from another computers, Edit file /etc/postgresql/9.4/main/postgresql.conf:
sudo vi /etc/postgresql/9.4/main/postgresql.conf
Find the lines:
[...] #listen_addresses = 'localhost' [...] #port = 5432 [...]
Uncomment both lines, and set the IP address of your postgresql server or set ‘*’ to listen from all clients as shown below:
listen_addresses = '*' port = 5432
Restart postgresql service to save changes:
sudo service postgresql restart
Manage PostgreSQL with phpPgAdmin
phpPgAdmin is a web-based administration utility written in PHP for managing PosgreSQL.
phpPgAdmin is available in default repositories. So, Install phpPgAdmin using command:
sudo apt-get install phppgadmin
By default, you can access phppgadmin using http://localhost/phppgadmin from your local system only. To access remote systems, do the following.
Edit file /etc/apache2/conf.d/phppgadmin:
sudo nano /etc/apache2/conf.d/phppgadmin
Comment the following line:
[...] #allow from 127.0.0.0/255.0.0.0 ::1/128
Uncomment the following line to make phppgadmin from all systems.
allow from all
Edit /etc/apache2/apache2.conf:
sudo vi /etc/apache2/apache2.conf
Add the following line:
Include /etc/apache2/conf.d/phppgadmin
Then, restart apache service.
sudo service apache2 restart
Configure phpPgAdmin
Edit file /etc/phppgadmin/config.inc.php, and do the following changes. Most of these options are self-explanatory. Read them carefully to know why do you change these values.
sudo nano /etc/phppgadmin/config.inc.php
Find the following line:
$conf['servers'][0]['host'] = '';
Change it as shown below:
$conf['servers'][0]['host'] = 'localhost';
And find the line:
$conf['extra_login_security'] = true;
Change the value to false:
$conf['extra_login_security'] = false;
Find the line:
$conf['owned_only'] = false;
Set the value as true.ru
$conf['owned_only'] = true;
Save and close the file. Restart postgresql service and Apache services.
sudo service postgresql restart sudo service apache2 restart
Now open your browser and navigate to http://ip-address/phppgadmin. You will see the following screen.
Login with users that you’ve created earlier. I already have created a user called “senthil” with password “ubuntu” before, so I log in with user “senthil”.
Now, you will access the phppgadmin dashboard.
Log in with postgres user:
That’s it. Now you’ll able to create, delete and alter databases graphically using phppgadmin easily.
Cheers!
