postgresql setup on btrfs

// under systemd postgresql btrfs CoW

Some time ago I came across http://www.pg-versus-ms.com/ and dreamed of the day postgres would make its way into my professional life.

The day is here! I am freelancing part time as a dev on a django project which uses postgres backend.

Here's my notes on setting up postgres on archlinux. It also ties into the recent posts on systemd.

Install postgres with your package manager

sudo pacman -S postgres

set a password for the postgres user

sudo passwd postgres

setup postgres data location. i have an 64gb ext4 usb drive for cases like this (i.e. where I don't want CoW or to bloat my main filesystem)

mkdir -p /media/storage_nocow/postgres/data
chown -R postgres:postgres /media/storage_nocow/postgres

now become postgres user and initialize database cluster

sudo -i -u postgres

initdb --locale en_US.UTF-8 -E UTF8 -D '/media/storage_nocow/postgres/data'

remember that systemd stuff I fumbled with? Create an adjusted systemd postgres.service file

cp /usr/lib/systemd/system/postgresql.service /etc/systemd/system/
sudo nano /etc/systemd/system/postgresql.service

replacing the following variables with the path to your non CoW filesystems

Environment=PGROOT=/media/storage_nocow/postgres
...
PIDFile=/media/storage_nocow/postgres/data/postmaster.pid

I also added a dependency to the filesystem mount, since it is external it is not guaranteed to be up with the system. Might as well add the error notification email while I am at it. Here's the first few lines of the unit file

cat /etc/systemd/system/postgresql.service | head -n 12

[Unit]
Description=PostgreSQL database server
After=network.target media-storage_nocow.mount
Requires=media-storage_nocow.mount
OnFailure=status-email-jotham@%i.service

[Service]
Type=forking
TimeoutSec=120
User=postgres
Group=postgres

check that it's up and running

systemctl status postgresql

● postgresql.service - PostgreSQL database server
   Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2015-06-22 17:19:01 EDT; 32s ago
  Process: 19878 ExecStop=/usr/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast (code=exited, status=0/SUCCESS)
  Process: 19887 ExecStart=/usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120 (code=exited, status=0/SUCCESS)
  Process: 19883 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
 Main PID: 19890 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─19890 /usr/bin/postgres -D /media/storage_nocow/postgres/data
           ├─19892 postgres: checkpointer process                         
           ├─19893 postgres: writer process                               
           ├─19894 postgres: wal writer process                           
           ├─19895 postgres: autovacuum launcher process                  
           └─19896 postgres: stats collector process

Jun 22 17:19:00 archLenFlex systemd[1]: Starting PostgreSQL database server...
Jun 22 17:19:00 archLenFlex postgres[19887]: LOG:  database system was shut down at 2015-06-22 17:18:59 EDT
Jun 22 17:19:00 archLenFlex postgres[19887]: LOG:  MultiXact member wraparound protections are now enabled
Jun 22 17:19:00 archLenFlex postgres[19887]: LOG:  database system is ready to accept connections
Jun 22 17:19:00 archLenFlex postgres[19887]: LOG:  autovacuum launcher started
Jun 22 17:19:01 archLenFlex systemd[1]: Started PostgreSQL database server.

and start and enable the postgresql service

systemctl start postgresql.service
systemctl enable postgresql.service

now, as postgres user (i.e. after sudo -i -u postgres)

createuser --interactive

set the username and password you desire for the postgres role

create the database

createdb yourDatabaseName -U yourPostgresUsername

run the psql prompt using the user you created

psql -U yourPostgresUsername

then connect to the database you just created

\c yourDatabaseName

Hopefully it worked!

Go Top