Category Archives: postgres

Installing postgres on Fedora

In Fedora 7+, postgres is insatlled by default. Its just about a few commands to get started. First of all you have to change the password for the postgres user, postgres. I am pasting the commands below:

#passwd postgres
#chown postgres /var/lib/pgsql/data/
#su – postgres
~initdb -D /var/lib/pgsql/data/
#/etc/rc.d/init.d/postgresql start

You can also install pgadmin3, which is a nice GUI for postgres:

#yum install pgadmin3

If you do not have the repository, you can add it from here:
http://yum.pgsqlrpms.org/reporpms/repoview/letter_p.group.html

I was inspired by this blog:
http://www.postgresonline.com/journal/index.php?/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html

Update As On 18th July 2014

After a long break, I am again back, trying to install Postgres. It has become lot easier. Just installed it using the rpm available on the site. It was smart enough to create the user postgres for me.

A few essential post install tweaks are necessary.

Note: The below steps have to be done while logging in as the unix user postgres.

Changing the authentication method from peer and ident to md5

By default, postgres has the unix user postgres as the only user. Now, you would like to change that. Edit the pg_hba.conf. Typically in Fedora it would be under the /var/lib/pgsql/data. Change it so that the below section looks like:

# “local” is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Then through psql, change the user role of the postgres user:

alter user postgres with password ‘mypassword’;

Now restart the service for these changes to take effect.

Making the database accept TCP/IP connection

Edit the postgresql.conf and un-comment the below line:

listen_addresses = ‘localhost’

After completing the above 2 steps, you should be all set!

[Fedora] Getting up and running with postgres in 2 mins

I am very fascinated with Postgres, right since I hard about it, around 2002. Compared to MySQL 3.25, which was and perhaps still is, more popular, Postgres fared a lot better in terms of SQL-compliance, foreign-key support [which was lacking in MySQL 3.25] etc. Also, Postgres is an ORDBMS, just like Oracle. You can do lot of cool stuff with Postgres.

For this exercise, we will assume:

Postgres user name = mypguser

Postgres data directory = /usr/local/pgsql/data

If you are using Fedora, Postgres will be installed by default. To verify, do a find on initdb and psql. If you do not have it installed, install it with yum or grab a tar ball from here and follow the instructions. Its pretty straight forward.

After you have installed, run the following commands:

#adduser mypguser
#passwd mypguser
#mkdir /usr/local/pgsql/data
#chown mypguser /usr/local/pgsql/data
#su – mypguser
$initdb -D /usr/local/pgsql/data

Starting Postgres

Postmaster should always be started as the underprivileged user mypguser.

$postgres -D /usr/local/pgsql/data >logfile 2>&1 &
To create a database:

createdb somedatabase
To start the command line tool:

psql somedatabase

Autostart Postgres as a service

Fedora should already have this script in /etc/init.d/postgresql.

The problem is that this assumes that the postgres user name is postgres and the data is located in /var/lib/pgsql/data. You can edit the script suitably and then enable it as a service, so that postmaster starts everytime your system boots up. You can also use the script that I have modified, just declared the user and data as variables. You can find it here.

Installing PgAdmin

PgAdmin is a nice gui frontend for administratin Postgres. You have to first add its yum repository. Follow these steps [you must be super user].

1. download rpm and add yum repository:
http://yum.pgsqlrpms.org/reporpms/repoview/letter_p.group.html
2. yum install postgis
3. yum install pgadmin3

You can also use phpPgAdmin.

For this article, I was inspired by:

http://www.postgresonline.com/journal/index.php?/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html