User Tools

Site Tools


build:postgres

PostgreSQL

While MySQL is the most popular Open Source SQL database, PostgreSQL is more powerful and mature. So while many Open Source projects only support MySQL, we prefer to use PostgreSQL when possible.

Installation

# Install all the pieces required for PostgreSQL server and client.
sudo apt-get install postgresql postgresql-doc postgresql-client
 
# Install drivers for PHP.
sudo apt-get install php5-pgsql php-mdb2-driver-pgsql

Configuration

# Make Postgres require passwords when accessing locally (via UNIX-domain socket).
sudo sed -e 's/\(local\W*all\W*all\W*\)ident/\1md5/' -i /etc/postgresql/8.4/main/pg_hba.conf 
sudo service postgresql reload
 
# Create a password file for root.
if sudo [ ! -f /root/.pgpass ]; then
    sudo sh -c "echo '#hostname:port:database:username:password' >/root/.pgpass"
    sudo chmod 600 /root/.pgpass
fi

Testing

# Set up some variables to use in our testing.
POSTGRESQL_TEST_DB='postgres_test'
POSTGRESQL_TEST_USER='postgres_test_user'
POSTGRESQL_TEST_PASSWORD="$(openssl rand 15 -base64 | tr '+/' '-_'))"
 
# Create a user. Could also do this via psql: "CREATE USER $POSTGRESQL_TEST_USER ENCRYPTED PASSWORD '$POSTGRESQL_TEST_PASSWORD';".
sudo sudo -u postgres createuser -SDR $POSTGRESQL_TEST_USER
 
# Create and test using a test database. Could also do this via psql: "CREATE DATABASE $POSTGRESQL_TEST_DB OWNER $POSTGRESQL_TEST_USER;".
sudo sudo -u postgres createdb --owner $POSTGRESQL_TEST_USER $POSTGRESQL_TEST_DB
 
# Make sure that the newly created database shows up in the list of databases.
sudo sudo -u postgres psql -l
 
# Create a DB table, and get some metadata about it.
sudo sudo -u postgres psql $POSTGRESQL_TEST_DB <<"EOF" # interactive
  SELECT datname FROM pg_database;
  SELECT table_name FROM information_schema.tables WHERE table_schema='public';
  CREATE TABLE test (id INTEGER, name VARCHAR);
  SELECT table_name FROM information_schema.tables WHERE table_schema='public';
  SELECT column_name FROM information_schema.columns WHERE table_name ='test';
EOF
 
# Remove the user and database to clean up. Note that you have to drop the DB before the owner of that DB.
sudo sudo -u postgres dropdb $POSTGRESQL_TEST_DB
sudo sudo -u postgres dropuser $POSTGRESQL_TEST_USER

TODO

See if we actually use the TCP port in any of our clients (because most local clients will use the UNIX domain socket). If not, see if we can disable listening on the TCP port.

Enable SSL. This doesn't matter too much, as long as we're only listening locally. But it looks like it's pretty simple to set up.

build/postgres.txt · Last modified: 2014/09/03 14:12 by Admin