Here we provide the details of the Linux VM on which we deploy PostgreSQL.

Context

About Us

We ...

have little linux admin expertise
are linux users (bash, vi, sed, awk, grep, etc does not scare us)
have few resources (money, people, time)

Therefore...

simple is important
automation is important (we use bash and python)

About the Application

The application is...

not huge
not complex
not trivial either
not particularly resource intensive

Our next release will be version 1.1.
We are using PG 8.3.7.
Christophe will be upgrading us to 9.0 for our 1.1 release.

You should also know that...

overnight ETL jobs load about 300 MB data
overnight map tile regeneration is read intensive on the DB

The application itself ...

will update and insert an average of 10-100 "street address records" per day
will issue 1K to 100K? reads per day (search, browse, report)
does a a modest amount of spatial processing (nearest streets, point in polygon, nearest addresses)
has no known or unresolved performance issue (we had a django ORM performance issue)

Security

the data is not sensitive
the cities network does contain sensitive information
the application is a business critical system

Downtime

Although the application is business critical, our downtime requirements are fairly lightweight.
Generally we can be down for an hour without people getting very excited.
If the application is down, the city will not be able to do things like issue building permits.
But let me be clear - we do not want people to get excited.
Downtime is scheduled off hours and communicated to our users well in advance.

Failover

Here again, the application is business critical but our requirements are fairly lightweight.
We are allowed to loose no more than 24 hours worth of data.
PG Experts has recommended we accomplish failover via log file shipping.

Facilities

We have 2 data centers (DC): San Diego (SD) and San Francisco (SF).
You'll need VPN credentials to access SF.
SD is "secondary", and is operated by Carinet.
SF is primary and is operated by the city.
We are running VM Ware 4.1.0
We using Centos 5.5.
We use Nagios 2.2 (yes, really! this is the city after all) for monitoring.

Now, About that VM...

We want a small core VM (< 1 GB?) so the VM copy/clone operations are network friendly and reasonably fast.
We want a minimal linux install (no desktop software, etc).

Recommendations

There are lots of details which are not specified here.
We'll lean on PG Experts to make recommendations as needed.
For example, for the file system we could ext3 or xfs.
I don't have an opinion on this, even after a lecture.
To help make decisions in these cases we prefer simplicity (see above) and consistency (see our other centos machines).

Memory

Size of database plus about 500 MB
We chose 8 GB.

Disk Partitions

PG Experts shall recommend a size for each partition.

Transaction Logs

Backups

OS

swap

system logging (/var/log)

PGDATA

To see how much space a server is currently using:
psql# select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;
I do not have any data growth information.
But we know that street addresses do not change much in the city.
We do have one area of the application that (ab)uses audit tables - but I would be a little surprised if this became an issue.

Session with Spencer and Jeff Frost

new vm
custom
provide name
select hypervisor
select data store
select vm version: 7
select OS: centos 4/5 64 bit
select number of processors: 2
specify RAM> 8GB
specify NIC: 1 NIC
scsi controller: LSI Logic parallel
create new virtual disk

store with VM
specify size (32 GB)
specify thin provisioning
advanced options (take defaults)
Edit VM setting before completion!
new CD/DVD
datastore ISO file
navigate to ISO
connect at power on
edit options
reserve memory: 8GB
right click on VM
power on
install centos
enter (to select grpahical installer)
skip media test
next
english/english
yes - erase all data
START partitioning
choose review and modify partitioning layout / Next
yes to warning
partitioning layout panel - choose reset
new / 4096; force to be primary; ext3
new; type swap; 4096 MB; not primary
/data; ext3; 16384 MB
/pg_xlog; ext3; 4096
/var; 2048; ext3
/home; 2048; ext3
END partitioning
Next
Next (install boot loader)
specify IP and netmask
specify gateway
specify DNS
uncheck gnome and all other (keep install small)
next
next
install will commence

install may take another 5-10 minutes -

At the of the install you will get a "first boot" dialog.
Authentication

select cache info
Next
Firwewall
enable
disable SE linux
customize

allow incoming: 5432:tcp,2241:tcp
Network configuration
System Services
cpuspeed: disable
bluetooth: disable
cups: disable
ip6tables: disable
Exit

view /etc/sysconfig/iptables

1. 1. 1. 1. make sure you can see the network
        $ ping google.com

If that fails...
$ cat /etc/sysconfig/network-scripts/ifcfg-eth0
NETMASK should be: 255.255.255.192
$ cat /etc/sysconfig/network
In SD, GATEWAY should be 209.126.178.65.

Now...
$ /etc/init.d/netowrk restart

Then ping google.com.

If not response...

$ ip route add to default via 209.126.178.65

Then ping google.com
64 bytes ....
Hooray!

Browser not supported