New Db Server

see PostgreSQL Admin

Setting Up a New Storm On Demand Image

NOTE: Where wget commands appear, try to locate the newest available version of a given rpm or package on the net before installing from urls seen here -- our urls may point to older versions of required software. Urls found here may also point to packages which do not meet your architecture, be sure to locate packages which correspond to your machine architecture. As of this writing (8 Aug 2012), Postgresql 9.1.4 was current.

Go to http://yum.postgresql.org/repopackages.php and download the appropriate rpm for the Postgresql yum repository. Install the yum repository:

cd /usr/local/src wget http://yum.postgresql.org/9.1/redhat/rhel-5-x86_64/pgdg-centos91-9.1-4.noarch.rpm rpm -ivh pgdg-centos91-9.1-4.noarch.rpm

Install Postgresql software from the yum repository using:

yum install postgresql91-*

Install additional useful yum repositories, namely epel. (Go to http://linux.mirrors.es.net/fedora-epel/ and choose the correct rpm to install):

cd /usr/local/src wget http://linux.mirrors.es.net/fedora-epel/5/x86_64/epel-release-5-4.noarch.rpm rpm -ivh epel-release-5-4.noarch.rpm

Install other useful software. Add or remove programs to this last as appropriate:

yum install vim-enhanced git

Initialize the database:

service postgresql-9.1 initdb chkconfig postgresql-9.1 on

Set up the base Postgresql configuration for the image. Configurations should be generic, you should expect to change certain configurations on an image-per-image basis with regards to image-dependent variables such as RAM and hard disk size.

In ~postgres/9.1/data/pg_hba.conf remove entries which use METHOD ident, add the following two lines under the IPv4 local connections section:

host all all 0.0.0.0/0 md5 host replication all 0.0.0.0/0 md5

When creating a new instance from the image you may need to add entries for certain machines you have in your database cluster or specific clients you expect to use the new database server.

In ~postgres/9.1/data/postgresql.conf modify the bellow entries to hold their correct values. The bellow values assume an 2 GB instance. Be sure to adjust these configurations on newly created instances which are not the same size as the base image. Add the given comments to these configuration entries for easy in-line reference:

` listen_addresses = '*'

shared_buffers = 512GB # min 128kB, 25% of physical ram

work_mem = 50MB

maintenance_work_mem = 256MB

effective_cache_size = 1GB # 50% of physical ram

default_statistics_target = 50 `

In the same file, adjust the configuration to support replication:

` wal_level = 'hot_standby'

wal_buffers = 16MB

checkpoint_segments = 256

checkpoint_completion_target = 0.9

archive_mode = on

archive_command = 'cd .'

max_wal_senders = 10

wal_keep_segments = 5000

hot_standby = on

`

Start the database to ensure it is running correctly with the given configuration:

service postgresql-9.1 start service postgresql-9.1 status

If it is running correctly, stop it, otherwise, review your configuration and continue to try starting the database until configuration problems are solved:

service postgresql-9.1 stop #when database runs correctly

Clone the pgbackup repository from GitHub:

cd ~postgres cd 9.1 git clone http://github.com/SkyPHP/pgbackup.git

Do not configure pgbackup -- this is outside the scope of creating a database image and is handled when setting up a new instance. New instance should also pull this repository to ensure it is up to date.

TODO: After research, decide whether or not we continue to need repmgr

Setting Up a New Instance

Refer to the configs in the previous section which are dependent on instance size. Change them appropriately.

Git pull pgbackup to ensure it is the latest version. Refer to the pgbackup readme to configure pgbackup if necessary.

Edit `/etc/hosts' file to include entries with local ip addresses for every database server in the database cluster as well as every web server which is expected to connect to the database cluster.

Start the database and check its status:

service postgresql-9.1 start service postgresql-9.1 status

TODO: Find out best way to add a slave to an existing cluster with or without repmgr

Ensure that the new database is replicating correctly by inserting test records on the master and seeing if they appear on the new node. If replication is not occurring, it may be necessary to add an entry to pg_hba.conf or to a remote .pgpass file.

Database should now be ready to roll-out.