postgresql
From SkyPHP
Contents |
Links
Install Postgresql w/ Slony and PostGIS
$ nano /etc/yum.repos.d/yum.system.repo
Go to sections that start with [base] and [updates], and at the end of each section, right after gpgkey statement add:
exclude=postgresql*
Then, do this:
yum erase postgresql*.i386 wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-7.noarch.rpm rpm -Uvh pgdg-centos-8.3-7.noarch.rpm yum install postgresql-* postgis wget ftp://195.220.108.108/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-DBD-Pg-2.8.1-1.el5.rf.x86_64.rpm wget ftp://fr2.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-DBI-1.611-1.el5.rf.x86_64.rpm wget http://packages.sw.be/perl-version/perl-version-0.82-1.el5.rf.x86_64.rpm rpm -Uvh perl-DBI-1.611-1.el5.rf.x86_64.rpm rpm -Uvh perl-version-0.82-1.el5.rf.x86_64.rpm rpm -Uvh perl-DBD-Pg-2.8.1-1.el5.rf.x86_64.rpm #install slony wget http://main.slony.info/downloads/2.0/source/slony1-2.0.4.tar.bz2 tar xf slony1-2.0.4.tar.bz2 cd slony1-2.0.4 ./configure --with-perltools=/usr/local/bin make make install ln --symbolic /etc/slon_tools.conf /usr/local/etc/slon_tools.conf chkconfig postgresql on
su - postgres initdb -D /var/lib/pgsql/data
Enable Replication
See slony for more information.
Install Postgresql w/ Mammoth Replicator and PostGIS
https://projects.commandprompt.com/public/replicator/wiki/QuickStart
https://projects.commandprompt.com/public/replicator/wiki/Documentation/1.8
$ nano /etc/yum.repos.d/yum.system.repo
Go to sections that start with [base] and [updates], and at the end of each section, right after gpgkey statement add:
exclude=postgresql*
$ wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm $ rpm -Uvh pgdg-centos-8.3-6.noarch.rpm $ yum erase postgresql* $ yum install mammoth-replicator* postgis
$ su - postgres $ initdb -D /var/lib/pgsql/data $ createdb dbname $ pg_ctl stop -m fast $ init-mammoth-database -D /var/lib/pgsql/data dbname $ pg_ctl start
/var/lib/pgsql/data_mcp/mcp_server.conf
mcp_master_address="192.168.1.2" mcp_authkey="postgrespassword" mcp_slave_addresses="0:192.168.1.3"
$ mcp_ctl start -D /var/lib/pgsql/data_mcp
/etc/rc.d/rc.local - on master and slave machines
su - postgres -c "/usr/bin/postgres -D /var/lib/pgsql/data &"
/etc/rc.d/rc.local - on only one machine (usually a slave)
su - postgres -c "/usr/bin/mcp_server -D /var/lib/pgsql/data_mcp start &"
/etc/apf/conf.apf
# common inbound ingress ports # add 5432
/var/lib/pgsql/data/pg_hba.conf
host all all 0.0.0.1 0.0.0.0 md5
crontab -e (master)
MAILTO=test@test.com 0 22 * * * /var/lib/pgsql/backups/pgbackup.sh
crontab -e (slave)
MAILTO=test@test.com 0 22 * * * vacuumdb -a -z
chmod a+x /var/lib/pgsql/backups/pgbackup.sh
#!/bin/sh # TODO dump tables into separate files, and keep schema and data separate vacuumdb -a -z DATE=`date +%Y%m%d%H%M` /usr/bin/pg_dumpall > /var/lib/pgsql/backups/backup.$DATE.sql cd /var/lib/pgsql/backups/ find ./ -ctime +3 -name "backup.*" -print | xargs /bin/rm -f
tuning 8.3 postgresql.conf
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
listen_addresses = '*' max_connections = 1000 shared_buffers = 8GB # 25% of physical ram work_mem = 50MB maintenance_work_mem = 256MB max_fsm_pages = 300000 max_fsm_relations = 2000 wal_buffers = 16MB checkpoint_segments = 256 # 4GB at a time (good for bulk load) checkpoint_completion_target = 0.9 # good for large checkpoint_segments effective_cache_size = 16GB # 50% of physical ram default_statistics_target = 50 log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_error_statement = log log_statement = 'mod' replication_database=dbname replication_mcp_address='192.168.1.3' replication_mcp_port=7777 replication_mcp_authkey=postgrespassword replication_mode=slave # master replication_slave_no=0 # comment this line on master replication_enable=true
postgres 9 streaming replication settings
vacuum_defer_cleanup_age = 100
whitelist the webserver ip on the db server and vice versa - or whitelist the private network on all servers
$ apf -a 192.168.1.0/24 $ service apf restart
immediately after restoring the database, before testing websites:
$ vacuumdb -vz dbname
backups using php
/.pgpass (nobody's home directory)
*:*:*:*:mypassword
- chmod 600
- chown nobody:wheel
