phato.blog

I had to find a new home for Teeview since its free plan was running full. So I would like it to use and external Postgres hosting on a VPS. So here’re simple processes to setup PostgreSQL on Ubuntu 12.04. Enjoy!

First, perform a quick update of the apt-get repository.

$ sudo apt-get update

Once apt-get has updated go ahead and download Postgres and its helpful accompanying dependencies.

$ sudo apt-get install postgresql postgresql-contrib

Postgres is installed, now time to set a new password for postgres user.

$ sudo -u postgres psql

> \password postgres  # and type in a new password
> \q  # to quit postgres console

Move on to configure postgres.

$ cd /etc/postgresql/9.1/main
$ cp pg_hba.conf pg_hba.conf.bak.original  # backup default configuration
$ cp postgresql.conf postgresql.conf.bak.original

Enable remote access from any IP address (with password required). Add this line to pg_hba.conf:

host  all   all   0.0.0.0/0   md5

Allow TCP/IP socket. Modify this line in postgresql.conf

listen_addresses='*'

And restart Postgres after finish configuration.

/etc/init.d/postgresql restart

Also make sure you enable a firewall for Postgres.

$ sudo ufw allow 5432
$ sudo ufw status

To                         Action      From
--                         ------      ----
###
5432                       ALLOW       Anywhere
5432                       ALLOW       Anywhere (v6)
###

And done! Now you have your Postgres running and can be accessed from any remote. Thanks to many posts help me setting all this up.

Refs: 1 , 2 , 3 , 4 , 5 , 6