Installing Postgres
This page outlines the steps I use to install and configure Postgres on a FreeBSD server or in a FreeBSD jail. At the time of writing this, the latest version of Postgres in FreeBSD ports, which I am using, is 8.4.3.
Install the ports
Install the ports with default options:
portmaster /usr/ports/databases/postgresql84-server/
And as always, remember to enable Postgres in rc.conf:
[tykling@wackbox163 ~]$ grep postgres /etc/rc.conf postgresql_enable="YES"
/etc/periodic.conf
I also add the following to /etc/periodic.conf
:
#for postgres daily_pgsql_backup_enable="YES" # do backup daily_pgsql_vacuum_enable="YES" # do vacuum
This makes Postgres run a full vacuum daily, as well as dump a daily backup (into /usr/local/pgsql/backups
by default).
Initializing Postgres
When you install the Postgres server, the post-install message says (among other things):
To initialize the database, run /usr/local/etc/rc.d/postgresql initdb You can then start PostgreSQL by running: /usr/local/etc/rc.d/postgresql start For postmaster settings, see ~pgsql/data/postgresql.conf NB. FreeBSD's PostgreSQL port logs to syslog by default See ~pgsql/data/postgresql.conf for more info
So before the database can be started, I need to run the following command:
/usr/local/etc/rc.d/postgresql initdb
However, when running Postgres in a jail there are problems related to Postgres using shared memory, read on for more.
Initializing Postgres In a FreeBSD Jail
When I tried calling the Postgres rc.d script with the "initdb" argument, I had some problems which I later found out was due to the fact that I was doing this in a jail. I was getting some errors about shared memory:
creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Function not implemented
I found the solution on the always great The FreeBSD Diary. I needed to change a sysctl on the jail host - not in the jail itself ofcourse - to allow the shared memory operation. Use:
[tykling@wackbox ~]$ sudo sysctl security.jail.sysvipc_allowed=1
to change the current value, and add:
security.jail.sysvipc_allowed=1
to /etc/sysctl.conf
to ensure it stays that way after the next reboot. I didn't need to restart the jail or anything after this change, it just immediately worked when I tried going back to rerun the initdb
command. I also add this rc.conf
line:
jail_sysvipc_allow="YES" # For PostgreSQL shared memory use in jails
Later I have found that when running multiple Postgres servers in different jails on the same physical FreeBSD machine, I needed some additional tweaking to make it work. First of all, again from the same article on The FreeBSD Diary, the uid # of the pgsql user in the jails needs to be different, or the different Postgres processes will corrupt each others memory. So I use vipw
to change the uid so they are different in the different jails, like so:
$ cat /usr/jails/*/etc/passwd | grep pgsql pgsql:*:2070:2070:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh pgsql:*:1070:1070:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh pgsql:*:70:70:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh
Here I have three different jails on the same machine, all running Postgres without affecting each other, because the uids are different.
More shared memory problems: When running multiple instances of Postgres on the same server in jails, the default amount of shared memory in FreeBSD isn't enough it seems. I found a solution which I am documenting here for future reference. I am not a fan of blindly copy/pasting stuff from around the web into config files. I generally try to graps the meaning of each line before I use it. But in this particular case, I have absolutely no idea if these values are correct or not, or even what they do (other than the fact that they are all related to shared memory). Some of them are from The FreeBSD Diary, others are from mailing lists and blogs around the web:
$ cat /etc/sysctl.conf | egrep -i "ipc|postgres" # for more shared memory for jails/PostgreSQL kern.ipc.shmall=65536 kern.ipc.shmmax=134217728 kern.ipc.semmap=4096 $ cat /boot/loader.conf | egrep -i "ipc|postgres" #for shared memory for postgresql kern.ipc.shmmni=2048 kern.ipc.shmseg=2048 kern.ipc.semaem=32767 kern.ipc.semvmx=65534 kern.ipc.semusz=184 kern.ipc.semume=80 kern.ipc.semopm=200 kern.ipc.semmsl=120 kern.ipc.semmnu=4096 kern.ipc.semmns=8192 kern.ipc.semmni=32767 kern.ipc.semmap=60
These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run initdb
with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.
A successful Postgres Initialization
For reference, a succesful initialization looks something like this:
[tykling@mail ~]$ sudo /usr/local/etc/rc.d/postgresql initdb The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. The database cluster will be initialized with locale C. The default text search configuration will be set to "english". creating directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 40 selecting default shared_buffers ... 28MB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/local/bin/postgres -D /usr/local/pgsql/data or /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start [tykling@mail ~]$ sudo /usr/local/etc/rc.d/postgresql start [tykling@mail ~]$ sudo su pgsql $ psql psql: FATAL: database "pgsql" does not exist $ createdb $ psql psql (8.4.3) Type "help" for help. pgsql=#