-
Notifications
You must be signed in to change notification settings - Fork 3
PostgreSQL Installation
Run the following when you login (and if you need to re-login), or add to .bashrc or .profile:
# Silence locale warnings.
export LANGUAGE=en_GB.UTF-8
export LANG=en_GB.UTF-8
export LC_ALL=en_GB.UTF-8Running Debian Squeeze.
Add to /etc/shorewall/rules:
ACCEPT net:46.43.37.19/28 $FW
ACCEPT net:46.43.37.21/28 $FW
ACCEPT net:46.43.37.26/28 $FW
ACCEPT net:46.43.37.28/28 $FW
And sudo /etc/init.d/shorewall restart.
Debian Squeeze includes only PostgreSQL 8.4. Backports has PostgreSQL 9.1, but not the postgresql-9.1-postgis package, so we need to compile PostGIS from source. Enable backports by adding deb http://backports.debian.org/debian-backports squeeze-backports main to /etc/apt/sources.list.
sudo aptitude update
sudo aptitude -t squeeze-backports install -y build-essential rsync postgresql-server-dev-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-9.1 libgeos-dev libproj-dev libxml2-dev
curl -O http://postgis.refractions.net/download/postgis-1.5.5.tar.gz
tar xvf postgis-1.5.5.tar.gz
cd postgis-1.5.5
./configure
make
sudo make installFollowing the tutorials by Brandon Konkle and PostgreSQL:
sudo -u postgres pg_dropcluster --stop 9.1 main
sudo -u postgres pg_createcluster --start -e UTF-8 9.1 main[[Set up the PostGIS template, create a superuser root and a user twfyl_user with strong passwords, and create the databases|PostGIS]].
Then, stop PostgreSQL.
We share WAL archives between master and slave over NFS.
sudo aptitude install -y nfs-common portmap
sudo vi /etc/fstabAdd this line to /etc/fstab:
46.43.37.28:/var/lib/postgresql/data/archive /mnt/share nfs rsize=8192,wsize=8192,timeo=14,intr 0 0
Then mount:
sudo mkdir /mnt/share
sudo mount /mnt/shareConfigure the master for streaming replication:
sudo -u postgres vi /etc/postgresql/9.1/main/postgresql.confEnsure the following settings are set:
listen_addresses = '*'
wal_level = hot_standby
checkpoint_segments = 16
archive_mode = on
archive_command = 'test ! -f /mnt/share/%f && cp -f %p /mnt/share/%f'
max_wal_senders = 5
wal_keep_segments = 32
We use the easiest to manage archiving option: network share. Brandon Konkle says max_wal_senders is the number of slaves (1), but PostgreSQL says it's the number of concurrent connections from the slave.
sudo -u postgres vi /etc/postgresql/9.1/main/pg_hba.confAppend:
host replication all 46.43.37.28/32 trust
host all all 46.43.37.19/32 trust
host all all 46.43.37.21/32 trust
host all all 46.43.37.28/32 trust
host all all 46.43.37.20/32 trust
We share WAL archives between master and slave over NFS. Reconfigure portmap to ensure it isn't bound to the loopback address.
sudo aptitude install -y nfs-kernel-server nfs-common portmap
sudo dpkg-reconfigure portmap
sudo /etc/init.d/portmap restart
sudo -u postgres mkdir -p /var/lib/postgresql/data/archive
id -u postgres
id -g postgres
sudo vi /etc/exportsUse the postgres user and group IDs to add a line like the following to /etc/exports, replacing UID and GID:
/var/lib/postgresql/data/archive 46.43.37.26/32(rw,async,no_subtree_check,all_squash,anonuid=UID,anongid=GID)
Restart NFS and export its shares:
sudo /etc/init.d/nfs-kernel-server restart
sudo exportfs -aConfigure the slave for streaming replication:
sudo -u postgres vi /etc/postgresql/9.1/main/postgresql.confEnsure hot_standby = on is set.
sudo -u postgres vi /var/lib/postgresql/9.1/main/recovery.confCreate with (replace PASSWORD):
standby_mode = 'on'
primary_conninfo = 'host=46.43.37.26 port=5432 user=root password=PASSWORD'
trigger_file = '/var/lib/postgresql/data/failover'
restore_command = 'cp -f /var/lib/postgresql/data/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/data/archive %r'
See the documentation on the behavior of the trigger file, and on continuous archiving and warm standby.
We opt for the simpler method over the Quick Master Restart method.
PostgreSQL should already be down on both the master and the slave. On the master:
sudo -i
rsync --rsh='ssh -p7012' -av --exclude pg_xlog /var/lib/postgresql/9.1/main/* cculture@46.43.37.28:~/data/
exitAnd on the slave:
sudo rsync -av ~/data/* /var/lib/postgresql/9.1/main/
sudo chown -R postgres:postgres /var/lib/postgresql/9.1/main/
sudo chmod -R 0700 /var/lib/postgresql/9.1/main/Start PostgreSQL on the slave first, then the master.
Run ps -ef | grep postgres on both master and slave, to ensure wal sender is running on the master and wal receiver is running on the slave. On the master, if there is no wal sender but there is a wal writer, either the slave didn't successfully connect to the master, or the slave is waiting on a WAL archive. Check the logs to troubleshoot:
sudo tail /var/log/postgresql/postgresql-9.1-main.logWe will use Taps to convert from MySQL to PostgreSQL.
# From running `rvm requirements`.
sudo aptitude update
sudo aptitude install -y build-essential openssl libreadline6 libreadline6-dev curl git-core zlib1g zlib1g-dev libssl-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt-dev autoconf libc6-dev ncurses-dev automake libtool bison subversion pkg-config
curl -L https://get.rvm.io | bash -s stable --ruby
source /home/cculture/.rvm/scripts/rvm
gem install --no-rdoc --no-ri taps sqlite3 pgRunning Debian Etch.
Update /etc/apt/sources.list to use only archive sources:
deb http://archive.debian.org/debian/ etch main non-free contrib
deb-src http://archive.debian.org/debian/ etch main non-free contrib
deb http://archive.debian.org/debian-security/ etch/updates main non-free contrib
deb-src http://archive.debian.org/debian-security/ etch/updates main non-free contrib
deb http://archive.debian.org/debian-backports etch-backports main
deb-src http://archive.debian.org/debian-backports etch-backports main
If you sudo aptitude update right now, you will get the error:
W: GPG error: http://archive.debian.org etch-backports Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY EA8E8B2116BA136C
W: You may want to run apt-get update to correct these problems
To clear the error, run sudo aptitude install -y debian-backports-keyring.
To silence locale warnings, run the following commands:
sudo aptitude install -y locales
sudo localedef -v -c -i en_GB -f UTF-8 en_GB.UTF-8
sudo locale-gen en_GB.UTF-8
Installing RVM fails quickly with BASH 3.2.25 required (you have 3.1.17(1)-release). Etch Backports doesn't have a more recent version. Instead, just install Ruby system-wide:
sudo aptitude install -y ncurses-term rubygemsSadly, rubygems in Etch is so old, it looks for gems on Rubyforge. So:
curl -O http://production.cf.rubygems.org/rubygems/rubygems-1.8.24.tgz
tar xvf rubygems-1.8.24.tgz
cd rubygems-1.8.24
sudo ruby setup.rbIf you previously did an upgrade against Etch Backports, add a -t etch-backports option before the install command:
sudo aptitude install -y build-essential ruby1.8-dev libsqlite3-ruby libsqlite3-dev libmysql-ruby libmysqlclient-dev
sudo gem install --no-rdoc --no-ri taps sqlite3 mysql2Running Debian Squeeze.
Debian Squeeze includes only PostgreSQL 8.4. Backports has PostgreSQL 9.1. Enable backports by adding deb http://backports.debian.org/debian-backports squeeze-backports main to /etc/apt/sources.list.
You may get errors and warnings if you run aptitude with sudo, so run it as root.
sudo su
aptitude update
aptitude install -y libpq-dev
exit
sudo /opt/ruby-enterprise-1.8/bin/gem install pg -v '0.14.0'Edit ~/sites/twfy_local/shared/config/database.yml to connect to the PostgreSQL master. Note: In Rails 2.3, the template option in database.yml seems to have no effect. (The activerecord-postgis-adapter gem, available to Rails 3+ only, respects the template option.)
We need to increase SHMMAX. See the PostgreSQL docs.
sudo sysctl -w kernel.shmmax=314572800
sudo sysctl -w kernel.shmall=2097152
sudo vi /etc/sysctl.confAdd the lines:
kernel.shmmax = 314572800
kernel.shmall = 2097152
Then, tune settings:
sudo -u postgres vi /etc/postgresql/9.1/main/postgresql.confSet shared_buffers to 256MB and effective_cache_size to 512MB.
taps server mysql2://root:PASSWORD@localhost/twfy_local_production user passtaps pull -g -e boundaries postgres://root:PASSWORD@localhost/twfy_local_production http://user:pass@kiribati.openlylocal.com:5000Create the boundaries table on twfy_local_production:
CREATE TABLE boundaries (
id integer NOT NULL,
area_type character varying(255),
area_id integer,
created_at timestamp without time zone,
updated_at timestamp without time zone,
hectares double precision
);
SELECT AddGeometryColumn('public', 'boundaries', 'boundary_line', 4326, 'POLYGON', 2);
CREATE SEQUENCE boundaries_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE boundaries_id_seq OWNED BY boundaries.id;
SELECT pg_catalog.setval('boundaries_id_seq', 1, false);
ALTER TABLE ONLY boundaries ALTER COLUMN id SET DEFAULT nextval('boundaries_id_seq'::regclass);
ALTER TABLE ONLY boundaries ADD CONSTRAINT boundaries_pkey PRIMARY KEY (id);Deploy the PostgreSQL version of OpenlyLocal to Liberia.
Get a download link for the "Boundary-Line" file from Ordnance Survey. Note: The new file uses NESS IDs, but the Rake task was written for SNAC IDs. You will likely have to use an old file we have. Then, from the Rails root:
curl -o bdline_gb.zip DOWNLOAD_LINK
unzip bdline_gb.zip
mv Data/district_borough_unitary_ward_region.* ~/sites/twfy_local/shared/data/boundary_line
cd ~/sites/twfy_local/current
RAILS_ENV=production bundle exec rake import_ward_boundariesInstall PostGIS from http://www.kyngchaos.com/software:postgres Need Postgresql 9.1. + PostGIS + GDAL
Installing pg is problematic. Do this first:
bundle config build.pg --with-pg-dir= /opt/local/lib/postgresql91
bundle