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