Linux VM for PostgreSQL

Owned by Paul McCullough (Unlicensed)
Last updated: Jun 13, 2017 by Samuel Valdez (Unlicensed)
3 min read

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
  1. view /etc/sysconfig/iptables
          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!