PostgreSQL is one among the leading open source RDBMS available for Windows, Linux and many other platforms. Installing PostgreSQL in Windows OS is much easier since PostgreSQL provides pre-packaged setup files for Windows. When it comes to Linux, people, especially newbies, find it difficult to install PostgreSQL because there is no all-in-one one-click installer available. Linux works differently and there are some manual work involved like, ‘changing repositories’, ‘installing dependencies’, etc
PostgreSQL 9.4 is also available, click here to know how to install it on Ubuntu
Let’s see how to install PostgreSQL 9.3 Server and PGAdmin3 on a clean Ubuntu OS in detail!
- Install dependency
Open Terminal and execute the following commands:
sudo apt-get update sudo apt-get -y install python-software-properties
- Add PostgreSQL 9.3 repository
Before installing PostgreSQL 9.3, we have to add the PostgreSQL repository where the packages are located.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Run the following command and note down the output. It is the codename of your Ubuntu release.
Replace the codename in the below command with the one you got from above command and run:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ codename-pgdg main" > /etc/apt/sources.list.d/postgresql.list'
- Install PostgreSQL 9.3 using aptitude
sudo apt-get update sudo apt-get install postgresql-9.3 pgadmin3
- Accessing Database from Terminal
Installation of PostgreSQL will automatically create a Unix/Ubuntu user, named postgres. We will have to login with this user in order to perform various database activities such as accessing database, taking and restoring backups, starting/stopping server etc.
We may need to reset the password for postgres user before we are able to login to that account.
The UNIX user, postgres is locked by default. Changing the password for this user will unlock the account. It is not preferred and even considered as a serious security threat.
To start with PostgreSQL, you can connect to the default database using the following command:
sudo -u postgres psql
For other activities like taking data base dump, you have to login as postgres UNIX user as shown below(assuming you have root user privileges):
sudo su postgres
- Accessing Database from pgAdmin
Just like the Unix user, PostgreSQL will also create a database user named postgres, which we should use for login to database for the first time.
To be able to access database from pgAgent3, we should follow either one of the following two options:
- Change the password for the postgres DB user
- Change the database access rights in the pg_hba.conf file
We will proceed with the second method, since setting password for postgres DB user is not preferred.
- After loging into postgres DB in terminal, run the following command to get the path to your pg_hba.conf file
show hba_file ;
In my case it returned: /etc/postgresql/9.3/main/pg_hba.conf
- Open the file using any text editors
sudo gedit /etc/postgresql/9.3/main/pg_hba.conf
And replace the two lines mentioned below:
local all all md5
local all all trust
host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 trust
If you are not able to find any lines similar to above two lines, adding the substitute lines at the top of the file will also do the job.
- Restart PostgreSQL server once.
- That’s it! You’ve installed PostgreSQL 9.3 on Ubuntu and now you can use pgAdmin3 to login to your database with any password.