This guide is one section of a larger guide to installing a cent 6.x server in virtual box for development purposes with different applications. The top level guide with links to all of the sections is here: https://gist.github.com/ryanguill/5149058 Some instructions in this guide may assume a configuration from other parts of the guide.
instructions taken (and modified slightly) from here: http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos and http://www.linuxquestions.org/questions/linux-server-73/change-postgresql-data-directory-649911/
start with a base install snapshot. You can install postgres and rabbitmq or redis on the same server, but both of those are going to fight for your ram, so make sure you tune appropriately. See the section called "Create a Clone" at https://gist.github.com/ryanguill/5149058 for more info.
Download latest production release (check here for the appropriate link: http://yum.pgrpms.org/repopackages.php)
Note: make sure you get the latest version! if you are using 9.2.x you need 9.2.4 or higher because of a security vulnerability. See this page for more info: http://www.postgresql.org/about/news/1456/
latest is 9.3.1 as of this writing.
# su -
# cd ~
# wget http://yum.pgrpms.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
install the repo
# rpm -i pgdg-centos92-9.3-1.noarch.rpm
edit base to exclude the standard install of postgres
# cd /etc/yum.repos.d
# vi CentOS-Base.repo
Add the following under [base] and [updates]
exclude=postgresql*
do a yum list to make sure you see a bunch of postgres 9.3 packages
# yum list postgres*
Do the install
# yum install postgresql93 postgresql93-devel postgresql93-server postgresql93-libs postgresql93-contrib
Before we get started we need to set some things up to point the database to our /data partition.
# mkdir /data/pgsql/
# mkdir /data/pgsql/9.3/
# sudo chmod 777 /data/pgsql/9.3/
# sudo chown user:user /data/pgsql/9.3/
Edit the startup script
# vi /etc/rc.d/init.d/postgresql-9.3
Find the section that looks like this:
# Set defaults for configuration variables
PGENGINE=/usr/pgsql-9.3/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/9.3/data
PGLOG=/var/lib/pgsql/9.3/pgstartup.log
And change it to look like this:
# Set defaults for configuration variables
PGENGINE=/usr/pgsql-9.3/bin
PGPORT=5432
PGDATA=/data/pgsql/9.3/data
PGLOG=/data/pgsql/9.3/pgstartup.log
Initialize the database
# service postgresql-9.3 initdb
start the server
# service postgresql-9.3 start
Note: If you encounter startup errors, check in /var/lib/pgsql/9.3/data/pg_log for clues.
The default home directory for the user postgres is at /var/lib/pgsql
The .bash_profile for the user postgres will look like this:
# vi /var/lib/pgsql/.bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.3/data
export PGDATA
We need to change it and to add the path to the bin directory, so make it look like this: Note: for the path export, we are prepending the new bin directories to the existing path, so that the appropriate psql gets picked up latter.
[ -f /etc/profile ] && source /etc/profile
PGDATA=/data/pgsql/9.3/data
export PGDATA
PATH=$HOME/bin:/usr/pgsql-9.3/bin:$PATH
export PATH
this allows you to invoke pg_ctl from the shell.
set the password for the postgres user
# su - postgres
# psql postgres postgres
you will now be in the REPL for postgres. Enter the following
alter user postgres with password 'postgres';
NOTE: the semicolon is important! it is required!
Check the data directory:
show data_directory;
It should return:
data_directory
----------------------
/data/pgsql/9.3/data
(1 row)
To quit
\q
# exit #to get you back to root
Note, you can also check the data directory like this:
# ps aux | grep post
You should see something like this:
postgres 1810 0.0 0.8 217720 9076 ? S 19:49 0:00 /usr/pgsql-9.3/bin/postmaster -p 5432 -D /data/pgsql/9.3/data
Now to alter the pg_hba.conf File
# cd /data/pgsql/9.3/data/
make a backup before we begin
# cp pg_hba.conf pg_hba.conf.orig
Lets alter it to make the method for local, host ipv4 and host ipv6 to all be MD5, and change the address mask for ipv4.
# vi pg_hba.conf
The original looks like this:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
Change it to: Make sure the ip address mask matches your network configuration!
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 192.168.0.0/24 md5
# IPv6 local connections:
host all all ::1/128 md5
Now you need to reload the configuration file.
You have a few options
# su - postgres
# pg_ctl reload
or
# psql postgres postgres
select pg_reload_conf();
or
# psql postgres postgres -c "select pg_reload_conf();"
Note, you will be prompted for your password
or you can always just restart the server
# service postgresql-9.3 restart
Now you need to open up access and connections. These changes are to get you going in development, you may need to change much more than this for a production system! see the 'other postgres resources' section for more info!
# cd /data/pgsql/9.3/data
# cp postgresql.conf postgresql.conf.orig
# vi postgresql.conf
To enable remote connections, uncomment out the listen_addresses line and leave it as an *
listen_addresses = '*' # what IP address(es) to listen on;
You can also change the port if you want from the default, 5432. If you are worried about security, this is a good idea. If you are using this as a development machine, leave it alone.
port = 5432
If you do change the port, you must restart the service.
# service postgresql-9.3 restart
To verify the listen addresses and port changes:
# psql postgres postgres
# show listen_addresses;
# show port;
# pgsql postgres postgres
create user myuser with password 'batman';
create database mytestdb owner=myuser;
\c mytestdb myuser
test it out
create table test (col1 varchar);
insert into test values('hello');
select * from test;
\dt --describe tables
Note: the psql syntax when entering the REPL is
# psql database user
So when you do
# psql postgres postgres
you are saying to connect to the postgres database as the user postgres.
# chkconfig --level 234 postgresql-9.3 on
# ln -s /usr/pgsql-9.3/bin/pg_config /usr/bin
# ln -s /var/lib/pgsql/9.3/data /var/lib/pgsql
# ln -s /var/lib/pgsql/9.3/backups /var/lib/pgsql
# vi /etc/sysconfig/iptables
Add the following lines before the first line that says REJECT - CHANGE THE IPADDRESS MASK as necessary for your network
# postgres
-A INPUT -s 192.168.0.0/24 -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
Make sure you restart iptables
# service iptables restart
This repo has some modified scripts that will vacuum and backup and optionally rotate the backups:https://bitbucket.org/ryanguill/pg_backup_scripts
(Original scripts were from here: http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux)
To install these scripts as a cronjob, follow these instructions.
First, create a place for the backups. Assuming that the user "user" will be running the scripts:
# mkdir /home/backups/database/postgresql/
# sudo chown -R user:user /home/backups/database/postgresql/
# sudo chmod -R 755 /home/backups/database/postgresql/
Now, lets create a .pgpass file
# echo '*:5432:*:postgres:postgres' >> ~/.pgpass
# sudo chmod 600 ~/.pgpass
Make sure that you have the following line in your pg_hba.conf:
host all all 127.0.0.1/32 md5
Now lets check out the scripts from git (if you dont have git installed, do yum install git)
# cd /opt
# sudo git clone [email protected]:ryanguill/pg_backup_scripts.git
This should create a folder called /opt/pg_backup_scripts with three files, a config and two backup scripts. pg_backup.sh will do a onetime backup, pg_backup_rotated.sh will do daily and weekly rotated backups. Edit the .config file to set these options.
Now, assuming you want to run the backups as the user "user", run the following to set up the crontab
# sudo crontab -u user -e
This will open up a most likely empty vi editor. Add the following line
00 08 * * * /opt/pg_backup_scripts/pg_backup_rotated.sh
The way crontab works, the first five arguments, seperated by spaces, are minutes, then hours, then days, months and day of week. Assuming UTC time, this will run at 8:00UTC every day, which is 02:00CST. Change it according to your timezone and needs.
This will create your backups in /home/backups/database/postgresql/ as yyyy-mm-dd-daily/ folders with the files contained inside.
Note: I am in no way conveying that this is the best way to do this, it very likely isn't - but it has worked at least a few times for me, so I am documenting it.
Drop the existing database, create an empty database and then restore the backup. This assumes a setup like in this guide. replace "mydb" with your actual database name.
# dropdb -i -h 127.0.0.1 -U postgres mydb
# createdb -h 127.0.0.1 -U postgres mydb
# pg_restore -C -d mydb -h 127.0.0.1 -U postgres mydb.BAK
You may get errors, but you most likely can ignore them.
http://www.slideshare.net/xzilla/the-essentialpostgresconf
http://www.postgresql.org/docs/9.0/static/performance-tips.html
http://www.revsys.com/writings/postgresql-performance.html
http://stackoverflow.com/questions/779311/performance-tuning-postgresql
http://wiki.postgresql.org/wiki/Streaming_Replication
http://www.postgresql.org/docs/9.1/static/high-availability.html
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
http://eggie5.com/15-setting-up-pg9-streaming-replication
http://www.navicat.com/en/products/navicat_pgsql/pgsql_detail_mac.html
http://postgresapp.com/
http://inductionapp.com/