Setup PostgreSQL on Fedora

These instructions work for Fedora 17. Fedora doesn't change that fast, so it may work for Fedora 18, 19, and 20.

Install

As root,

yum -y install postgresql-server postgresql-contrib

The -contrib package isn't technically necessary, but you're probably going to need it at some point.

Initialize the db

If you just start the service, it won't work. You need to initialize it first.

$ sudo postgresql-setup initdb
Initializing database ... OK

pg_hba.conf

You'll need to initialize the db before you can see the config files.

pg_hba.conf controls how users are allowed to access the database.

Edit the file /var/lib/pgsql/data/pg_hba.conf either as root or postgres. Change the lines ending in 'ident' to end with 'md5' so people can login with their password, or rather, an md5 of their password.

Starting / Stopping / Etc...

The service is postgresql.service in systemctl's system.

To start/stop/restart, just use:

sudo systemctl command postgresql.service

Don't forget to 'enable' it if you want it to run when you reboot.

Creating a User and DB

Login as postgres. The easiest way is with a command like:

sudo su - postgres

Check that you can login.

-bash-4.2$ psql
psql (9.1.6)
Type "help" for help.

postgres=#

That shows that you can connect as an admin (the # prompt). You need to be able to connect as an admin to do the following.

Hit CTRL-d to logout.

Now, as postgres, run:

createuser -P <username>

This will prompt for a password. It will also ask whether the user is allowed to create more roles, etc...

To create a database, use the createdb command.

Make it externally available

You'll need to accept incoming connections. It's best to think it will be exposed to the world, so 0.0.0.0/0 will work in pg_hba.conf

You'll also need to open up the ports in your firewall.

Backups

Backuping up PostgreSQL is NOT as simple as copying the data files. In fact, make sure you do NOT back the data files up. It's just a waste of space.

You'll want to use pg_dump to dump your database, likely storing it in a file gzipped. Bonus points if you backup on to another host. IE:

ssh <postgresql host> pg_dump <blah blah>  | gzip ...

Whether you want to run the gzip on the db host or not depends on whether you have cycles to spare or network to spare.

Replications

There are a number of replication options. I'm not familiar with the current state to say anything useful.