Oasis: postgreSQL setup Fedora

Fedora has different ways of approaching things. And one of them is the setting up of postgreSQL database. Unlike in Ubuntu, Fedora requires the database administrator to set up things the way it expected them to be. The steps one might follow are:

1. Decide the location of the database directory,
2. Initialize the database using this connection,
3. Modify /etc/profile file, to start the database without requiring the -D option,
4. Modify pg_hba.conf
5. Start the database manually using pg_ctl.
6. Change postgres password.
7. Securing connections.
8. Start the database automatically.

Decide the location of the database setup

This is a file or directory organization issue, you can put the postgreSQL database anywhere you like as long as it does not require super user previlege and that you have access too. But for simplicity, let us assume that you wanted to put the database in the directory mypgdb in your local directory. So, if your linux user name is dbadmin, you might want to put the directory in /home/dbadmin/mypgdb. And since this directory is not created by default, open a terminal and in your home directory type:

mkdir mypgdb

Initialize the database using this connection
After creating the directory, in the terminal still, type:

initdb -D /home/dbadmin/mypgdb

This command will prepare the directory mypgdb for use by postgre.

Modify /etc/profile file, to start the database without requiring the -D option

You can now start the database by typing:
pg_ctl -D /home/dbadmin/mypgdb start -o -i

This command will start the database and will accept connections.
The database service could be started without the -D option, how? By exporting and setting the PGDATA variable. Everytime this variable is set you can to start the database without including the -D option. Neat! But that would include 2 commands now, setting and exporting the PGDATA and starting the database.
To automatically set the PGDATA, we must add an entry to the /etc/profile. Using your favorite editor, say vim, type the command:

vim /etc/profile

Add a new entry at the end of the file like this:

export PGDATA=/home/dbadmin/mypgdb

After that, save your work and re-login from the Fedora system for the changes to take effect.

Modify pg_hba.conf

The pg_hba.conf file determines how users are authenticated in the database system. There are many types of possible authentication but for this discussion let us try to focus on trust and md5, for more information about the pg_hba.conf click here.
Briefly described, the trust type will allow any registered users of the database system to login without requiring a password. This type of authentication is good for the initial set-up specifically on changing the password of the default user postgres. While md5 is the recommended type when you are allowing login request from remote users. This option will ask for the user to enter its corresponding password and more. The password is encrypted as it is passed from the remote computer to your database system. So, information thieves will have a hard time cracking your password if ever they intercepted it. Nice, isn't it?
To change the default password of the user postgres, Follow these steps,
1. Stop the database.
pg_ctl stop
2.Change the authentication entry in the pg_hba.conf file, from md5 --> trust. You can find the pg_hba.conf in the database directory that you specified (i.e., /home/dbadmin/mypgdb).
3. Start the database.
pg_ctl start -o -i
4. Then you can read from my previous blog on how to do it using pgAdmin3 (specifically steps f - h). If you haven't installed it yet type:
sudo yum install pgadmin3 -y

Securing connections.

Now that you are in control of the postgres account the next thing you need to do is to make sure that subsequent connections be authenticated. So, we need to access the pg_hba.conf file and change the previously trust to md5.

Then, stop and start the database.

Start the database automatically.

Unlike in Ubuntu, the postgreSQL database does not automatically start. If you want this to happen, you have to put an entry in the Sessions containing the command to start the database. To do this, go to System --> Preferences --> Personal --> Sessions, you would then be prompted by the interface shown below: