Installing Postgres

From TykWiki
Jump to navigationJump to search

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=#