If you've reached this post, you probably already know… Postgres is generally considered better than MySQL. MySQL is more common, and therefore a bit easier to get up and running (it'll come out of the box with a lot of hosts). Here's how to setup Postgres on Media Temple DV 4.0, running CentOS 5.8.
I'm new to Postgres. I'm a programmer, not a DB Admin, or a SysAdmin. I'm writing this post primarily for my own reference, and to help solidify my own understanding. Keep in mind I'm no expert!!!
First up, the date today is 8 Aug, 2013. I'm working on a Media Temple DV 4.0, with Plesk 11 installed. I'm running CentOS 5.8, 64 bit version, using Apache as my webserver. If those details don't match up closely with what you're using, ignore this post!
Media Temple have some instructions on how to install postgres - but following them verbatim will make the yum package manager install a pretty old version, which probably isn't what you want. There's a few other issues with that set of instructions, too, such as the fact that passwords are encrypted in Plesk 11 (they weren't in earlier versions), so the commands media temple use regarding passwords no longer work. So I'd ignore that guide all together.
ssh into your server, and run
yum list postgres*
This will list all packages yum knows about, starting with postgres.
To check you're runnning the 64 bit version of CentOS, execute uname -a and you should see x86_64 somewhere in the output. If you're on a Media Temple DV 4.0, you'll be running 64 bit.
If we're running 64 bit, then really the only packages we're interested in are the ones ending in x86_64. So, we could list them alone with
yum list postgres*x86_64
There's a bunch of versions, and we're only after the latest. Check the lastest stable release of Postgres on the official website. As of today (8 Aug 2013), it's 9.2.4.
However, the latest version we've got listed in yum is 8.4, which is earlier than we want.
The reason for this is that REHL doesn't try to stay up to date - it has very stable versioning. The true 'latest stable version' won't be available in yum for quite a while after it's released.
Luckily, the Postgres maintainers provide newer releases for RHEL and Fedora, here. The RPM we're after is called Red Hat Enterprise Linux 5 - x86_64, and it's available at http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-redhat92-9.2-7.noarch.rpm.
There are better instructions on the Postgres Wiki for how to install lastest versions using yum.
We'll basically follow along with those instructions, but I'll add the exact commands I use, for my setup (Media Temple DV 4.0, CentOS 5, Postgresql 9.2)
First, you might want to make a date-stamped backup:
cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.BAK.2013-08-08
Now, let's open /etc/yum.repos.d/CentOS-Base.repo with a text editor. I'll use nano.
nano /etc/yum.repos.d/CentOS-Base.repo
At the end of both the [base] and [updates] sections, add the suggested line, and add a comment on the end if you want, to remind yourself of why it's there:
exclude=postgresql* # added on 2013-08-08, based on http://wiki.postgresql.org/wiki/YUM_Installation
Save with Control-O, and exit with Control-X.
For 9.2.6 on CentOS 5, we run:
curl -O http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm
~~~
You'll obviously need to adjust the URL if you want a different packed. Check which package you want [here](http://yum.postgresql.org/repopackages.php).
Now, install it - use `rpm -ivh `, followed by the last part of the URL in the above command. So,
~~~markup
rpm -ivh pgdg-centos92-9.2-6.noarch.rpm
Now yum should be able to see the version we installed. Check, with
yum list postgres*
yum install postgresql92 postgresql92-server postgresql92-devel
NOTE: you may not need the postgresql92-devel option. I do need it, for the Rails pg gem, which I'll install later. Either way, it doesn't really hurt to have it there.
Now, initialise the database with
service postgresql-9.2 initdb
Now, make PostgreSQL start automatically when the Operating System starts:
chkconfig postgresql-9.2 on
Note that we'll be able to start / stop / restart / reload with the following commands:
service postgresql-9.2 start
service postgresql-9.2 stop
service postgresql-9.2 restart
service postgresql-9.2 reload
If those commands fail, this should also do it:
/etc/init.d/postgresql-9.2 start
/etc/init.d/postgresql-9.2 stop
/etc/init.d/postgresql-9.2 restart
/etc/init.d/postgresql-9.2 reload
So now, start the Postges server:
service postgresql-9.2 start
Note that the path to the postgres configuration file will be /usr/pgsql-9.2/bin/pg_config.
At this point, you should read through the official tutorial. A lot of it is basic SQL stuff, but skim it anyway and pay particular attention to Postgres-specific stuff. There's also a first steps after installation wiki page - though it's not official (anyone can edit the wiki), and I personally found it to be a bit confusing.
I plan on writing another post on questions I had after installing postgres, and suggested first steps. But for now, I'm done."
For lots of stuff (creating users, database, etc), you'll have to be using ssh as the postgres user. You do this by executing su - postgres.
By default, postgres will use ident authentication, which you won't be familiary with if you're coming from MySQL. You can read a great article about it here. You can read about all of Postgres' authentication means here. Because I'm coming from MySQL, and I'm keen to get this thing up and running, for now I'm just going to change ident to md5 authentication, which basically means it'll work the same way as I'm used to in MySQL.
To change that, we'll need to edit pg_hba.conf:
nano /var/lib/pgsql/9.2/data/pg_hba.conf
Find the line that says:
host all all 127.0.0.1/32 ident
and change ident to md5. You might like to leave the existing line there commented out for future reference:
# host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 md5
Save with Control-O, and exit with Control-X.
Then, restart postgres with service postgresql-9.2 restart.
You may find you need to specify the path to the postgres config, something like this (adjust the version number for your situation):
gem install pg -v '0.15.0' -- --with-pg_config=/usr/pgsql-9.2/bin/pg_config
If you get errors still, you may want to try Christian's solution for Postgres gem on CentOS 5.8, here (ie, export PATH=/usr/pgsql-9.2/bin:$PATH and then bundle install)
Type pg_config at the terminal. If you get "command not found" you'll need to add pg_config to your path. You can add it for the current session only with this:
export PATH=$PATH:/usr/pgsql-9.2/bin/
However, you'll probably want to add it to your path permanently, and I'll let Google help you out there.