Installing Postgres - Revision history

Tykling at 14:21, 9 May 2010

2010-05-09T14:21:22Z

← Older revision		Revision as of 14:21, 9 May 2010
Line 41:		Line 41:
	</pre>		</pre>

	== Initializing Postgres In a FreeBSD Jail ==		So before the database can be started, I need to run the following command:
			<pre>
			/usr/local/etc/rc.d/postgresql initdb
			</pre>

			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:		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:

Tykling at 14:16, 9 May 2010

2010-05-09T14:16:26Z

← Older revision		Revision as of 14:16, 9 May 2010
Line 99:		Line 99:
	These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run <code>initdb</code> with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.		These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run <code>initdb</code> 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:		For reference, a succesful initialization looks something like this:
	<pre>		<pre>

Tykling at 14:14, 9 May 2010

2010-05-09T14:14:09Z

← Older revision		Revision as of 14:14, 9 May 2010
Line 41:		Line 41:
	</pre>		</pre>

	When I tried calling the rc.d script with the "initdb" argument, I had some problems which I later found out was due to the fact that ~~Postgres is running~~ in a jail. I was getting some errors about shared memory:		== 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:

	<pre>		<pre>

Tykling at 13:36, 9 May 2010

2010-05-09T13:36:31Z

← Older revision		Revision as of 13:36, 9 May 2010
Line 97:		Line 97:

	These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run <code>initdb</code> with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.		These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run <code>initdb</code> with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.

			For reference, a succesful initialization looks something like this:
			<pre>
			[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=#
			</pre>

Tykling at 13:18, 9 May 2010

2010-05-09T13:18:32Z

← Older revision		Revision as of 13:18, 9 May 2010
Line 58:		Line 58:
	</pre>		</pre>

	to <code>/etc/sysctl.conf</code> 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 <code>initdb</code> command.		to <code>/etc/sysctl.conf</code> 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 <code>initdb</code> command. I also add this <code>rc.conf</code> line:
			<code>
			jail_sysvipc_allow="YES" # For PostgreSQL shared memory use in jails
			</code>

	~~'''Update''':~~ 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 [http://www.freebsddiary.org/jail-multiple.php 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 <code>vipw</code> to change the uid so they are different in the different jails, like so:
			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 [http://www.freebsddiary.org/jail-multiple.php 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 <code>vipw</code> to change the uid so they are different in the different jails, like so:
	<pre>		<pre>
	$ cat /usr/jails/*/etc/passwd \| grep pgsql		$ cat /usr/jails/*/etc/passwd \| grep pgsql

Tykling at 13:02, 9 May 2010

2010-05-09T13:02:49Z

← Older revision		Revision as of 13:02, 9 May 2010
Line 1:		Line 1:
			[[category: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.		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.

Tykling at 13:00, 9 May 2010

2010-05-09T13:00:37Z

@@ Line 14: / Line 14: @@
 </pre>
 == Initializing Postgres ==
@@ Line 84: / Line 92: @@
 These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run <code>initdb</code> with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.

Tykling at 12:57, 9 May 2010

2010-05-09T12:57:39Z

← Older revision		Revision as of 12:57, 9 May 2010
Line 49:		Line 49:
	</pre>		</pre>

	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.		to <code>/etc/sysctl.conf</code> 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 <code>initdb</code> command.

	'''Update''': 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 [http://www.freebsddiary.org/jail-multiple.php 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 <code>vipw</code> to change the uid so they are different in the different jails, like so:		'''Update''': 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 [http://www.freebsddiary.org/jail-multiple.php 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 <code>vipw</code> to change the uid so they are different in the different jails, like so:

Tykling at 12:56, 9 May 2010

2010-05-09T12:56:31Z

← Older revision		Revision as of 12:56, 9 May 2010
Line 1:		Line 1:
	At the time of writing this, the latest version of Postgres in FreeBSD ports is 8.4.3. ~~After installing~~ it from ~~ports~~ it is ~~neccesary~~		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:

			<pre>
			portmaster /usr/ports/databases/postgresql84-server/
			</pre>

			And as always, remember to enable Postgres in rc.conf:
			<pre>
			[tykling@wackbox163 ~]$ grep postgres /etc/rc.conf
			postgresql_enable="YES"
			</pre>


			== Initializing Postgres ==
			When you install the Postgres server, the post-install message says (among other things):
			<pre>
			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
			</pre>

			When I tried calling the rc.d script with the "initdb" argument, I had some problems which I later found out was due to the fact that Postgres is running in a jail. I was getting some errors about shared memory:

			<pre>
			creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Function not implemented
			</pre>

			I found the solution on the always great [http://www.freebsddiary.org/jail-multiple.php 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:

			<pre>
			[tykling@wackbox ~]$ sudo sysctl security.jail.sysvipc_allowed=1
			</pre>

			to change the current value, and add:
			<pre>
			security.jail.sysvipc_allowed=1
			</pre>

			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.

			'''Update''': 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 [http://www.freebsddiary.org/jail-multiple.php 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 <code>vipw</code> to change the uid so they are different in the different jails, like so:
			<pre>
			$ 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
			</pre>
			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 [http://www.freebsddiary.org/jail-multiple.php The FreeBSD Diary], others are from mailing lists and blogs around the web:
			<pre>
			$ 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
			</pre>

			These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run <code>initdb</code> with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.












	== /etc/periodic.conf ==		== /etc/periodic.conf ==

Tykling: Created page with 'At the time of writing this, the latest version of Postgres in FreeBSD ports is 8.4.3. After installing it from ports it is neccesary == /etc/periodic.conf == I add the followi…'

2010-05-09T12:40:03Z

Created page with 'At the time of writing this, the latest version of Postgres in FreeBSD ports is 8.4.3. After installing it from ports it is neccesary == /etc/periodic.conf == I add the followi…'

New page

At the time of writing this, the latest version of Postgres in FreeBSD ports is 8.4.3. After installing it from ports it is neccesary

== /etc/periodic.conf ==
I add the following to <code>/etc/periodic.conf</code>:
<pre>
#for postgres
daily_pgsql_backup_enable="YES" # do backup
daily_pgsql_vacuum_enable="YES" # do vacuum
</pre>