I’ve recently started moving back to FreeBSD from Ubuntu. As it’s a large move and I’ve not touched FreeBSD for quite a few years, baby steps are required. With that in mind I’ve started small with my home server and once I’m comfortable with that and how things work I’ll look at moving my online server. The reasons for the move will have to wait for another post 🙂
Previous the home server had both MySQL and PostgreSQL installed and running as that reflected how the online server was setup. With this new start I’ve decided to skip the MySQL server and instead move totally to PostgreSQL. However, the change isn’t without it’s issues and challenges – mainly around getting it installed!
Build
The build was easy enough. I’m using the ports to try and keep things as up to date and configurable as possible as time isn’t a large factor in this build.
$ cd /usr/ports/databases/postgresql95-server $ sudo make install clean
After answering the various configuration choices all went well and the build completed. Reading the post install text will reveal you need another step before you can do much.
Initdb
After installing I wanted to choose a different location for the actual database files – one that would be on my zfs pools with all their extra protections. With previous installations I would have edited the configuration file to point to the new location, but looking around there were no configuration files! Hmmm. As a first step, I decided to just run the initdb command and see what was installed.
$ sudo /usr/local/etc/rc.d/postgresql initdb
Files
After running all the expected files were present but the database cluster had also been created under the same tree. Not quite what I wanted. Time to look at the config script and figure out what was going on…
In the startup script I found this block, which points to the “usual” FreeBSD configuration mechanism as being usable for the changes I wanted.
load_rc_config postgresql # set defaults postgresql_enable=${postgresql_enable:-"NO"} postgresql_flags=${postgresql_flags:-"-w -s -m fast"} postgresql_user=${postgresql_user:-"pgsql"} eval postgresql_data=${postgresql_data:-"~${postgresql_user}/data"} postgresql_class=${postgresql_class:-"default"} postgresql_initdb_flags=${postgresql_initdb_flags:-"--encoding=utf-8 --lc-collate=C"}
Configuration
After finding that the configuration settings would be honoured (as I should have expected) I just needed to add them.
$ sudo vi /etc/rc.conf ... postgresql_enable="YES" postgresql_data=/usr/local/pgdata postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
Close, but not quite…
After making the changes I tried again.
$ sudo service 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". Data page checksums are disabled. creating directory /usr/local/pgdata ... initdb: could not create directory "/usr/local/pgdata": Permission denied
Hmm, OK, so it’s almost working but as the PostgreSQL commands are run as the pgsql user and not root, the inability to create a new directory isn’t unexpected. I guess what I need to do is create the directory, change ownership and then run the initdb command.
$ sudo mkdir /usr/local/pgdata $ sudo chown pgsql /usr/local/pgdata $ sudo service postgresql initdb ... Success. You can now start the database server using: /usr/local/bin/pg_ctl -D /usr/local/pgdata -l logfile start
Success
The database was installed, configuration files are all in the same location and while for this post I just used /usr/local/pgdata I can now create the database where it needs to be. Interestingly though, removing the /usr/local/pgsql directory caused the initdb script to fail, so the directory needs to be present, though it stays empty throughout the process, probably due to being listed as the home directory of the pgsql user.
For future installs, this will be my process
- build and install postgresql
- set postgresql variables in /etc/rc.conf
- create postgresql data directory and change ownership
- run postgresql initdb
- start postgresql
Update
It was pointed out to me that I probably wanted to set the encoding of the database to UTF-8, so I needed to add this line to my /etc/rc.conf file
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
This line is given at the top of the script but I’d missed it earlier.