# [PC-BSD] PostgreSQL won't start at boot.



## nickednamed (Feb 22, 2012)

Despite my best efforts I have not been able to get PostgreSQL to start at boot time, and *I* cannot work out why. I'm running PostgreSQL 8.3.16_2 on FreeBSD 9.0-RELEASE [actually PC-BSD Isotope] and *I* installed PostgreSQL during the system installation [IE not myself]. My goal was to install and set up OpenERP, using PostgreSQL as my database.

I am able to start the database server [manually], apache [at boot], the OpenERP server and finally the OpenERP web client [both manually - as I can't start them before the database server anyway]. However, I am unable to get PostgreSQL to start at boot. I have done the following:


```
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
```

So far so good. However, I am unable to start the service in the background with a logfile with the command above or the following [as user "postgres"]:


```
pg_ctl start -l logfile
```

It says something like "cannot create logfile: access denied." [Can't run the command right now to see exactly what it says].

*T*he command 
	
	



```
$ ls -l /usr/local/pgsql/data
```

gives me the following:


```
total 76
-rw-------  1 postgres  pgsql      4 Feb  9 11:07 PG_VERSION
drwx------  9 postgres  pgsql    512 Feb 22 13:37 base
drwx------  2 postgres  pgsql   1024 Feb 22 14:16 global
-rw-r--r--  1 postgres  pgsql     20 Feb 16 13:09 logfile
drwx------  2 postgres  pgsql    512 Feb  9 11:07 pg_clog
-rw-------  1 postgres  pgsql   3431 Feb 19 20:19 pg_hba.conf
-rw-------  1 postgres  pgsql   1460 Feb  9 11:07 pg_ident.conf
drwx------  4 postgres  pgsql    512 Feb  9 11:07 pg_multixact
drwx------  2 postgres  pgsql    512 Feb  9 11:07 pg_subtrans
drwx------  2 postgres  pgsql    512 Feb  9 11:07 pg_tblspc
drwx------  2 postgres  pgsql    512 Feb  9 11:07 pg_twophase
drwx------  3 postgres  pgsql    512 Feb 22 14:04 pg_xlog
-rw-------  1 postgres  pgsql  16860 Feb 19 20:13 postgresql.conf
-rw-------  1 postgres  pgsql     53 Feb 22 13:30 postmaster.opts
-rw-------  1 postgres  pgsql     48 Feb 22 13:30 postmaster.pid
```

So I'm pretty sure "postgres" should have permissions to do this. Any ideas what I'm doing wrong?

But my major problem is that I can't get PostgreSQL to load at boot. I have done the following, as required:


```
# echo 'postgresql_enable="YES"' >> /etc/rc.conf
```

I have also added the "freebsd" start script to 
	
	



```
/usr/local/etc/rc.d/postgresql
```
 as instructed. *T*the contents of that file are as follows [*I* haven't edited it]:


```
$ cat /usr/local/etc/rc.d/postgresql 
#! /bin/sh

# PostgreSQL boot time startup script for FreeBSD.  Copy this file to
# /usr/local/etc/rc.d/postgresql.

# Created through merger of the Linux start script by Ryan Kirkpatrick
# and the script in the FreeBSD ports collection.

# $PostgreSQL: pgsql/contrib/start-scripts/freebsd,v 1.4 2004/10/01 18:30:21 tgl Exp $

## EDIT FROM HERE

# Installation prefix
prefix=/usr/local/pgsql

# Data directory
PGDATA="/usr/local/pgsql/data"

# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

## STOP EDITING HERE

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster (we do NOT use pg_ctl for this,
# as it adds no value and can cause the postmaster to misrecognize a stale
# lock file)
DAEMON="$prefix/bin/postmaster"

# What to use to shut down the postmaster
PGCTL="$prefix/bin/pg_ctl"

# Only start if we can find the postmaster.
test -x "$DAEMON" || exit 0

case $1 in
    start)
	su -l $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
	echo -n ' postgresql'
	;;
    stop)
	su -l $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
	;;
    restart)
	su -l $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"
	su -l $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
	;;
    status)
	su -l $PGUSER -c "$PGCTL status -D '$PGDATA'"
	;;
    *)
	# Print help
	echo "Usage: `basename $0` {start|stop|restart|status}" 1>&2
	exit 1
	;;
esac

exit 0
```


What have *I* missed?


----------



## idle (Feb 22, 2012)

I had never dealt with PC-BSD, and can not know how does it install soft during installation, but I guess it's done in wrong way, since there is no start script, no user and no directory. 
You'd better remove this one, and install from ports.


----------



## nickednamed (Feb 25, 2012)

Thanks.

I have tried all I can and can't go any further by myself without some form of divine inspiration / intervention. 

I have taken your advice, and I'm installing it from the beginning, this time in a jail.

So no doubt I will be posting something about that here soon.

Thanks again.


----------



## grumpybozo (Feb 27, 2012)

*Maybe not the best plan...*

Installing PostgreSQL in a jail is not such a great idea. It uses SysV IPC shared memory and the whole OS shares a SysV IPC shared memory space. By default, jails can't use SysV IPC shared memory, and while you can turn it on for jails with a sysctl setting, doing so makes "jails" more like "velcro attached monitoring bracelets". 

I don't know if PC-BSD normally uses the FreeBSD Ports subsystem, but if it does, installing from Ports is almost surely your best option. The Ports are not always perfect, but they are the most widely used means of installing freeware on FreeBSD. They have some oversight, their maintainers generally take care of all the platform-specific adornments like startup scripts and user ID's for you, and there are mature tools for managing port-installed software packages for security issues and interdependencies.


----------



## HarryE (Feb 27, 2012)

Check the content of /usr/local/pgsql/data/postmaster.log file after a [CMD=]/usr/local/etc/rc.d/postgresql onestart[/cmd]


----------



## paulfrottawa (Nov 5, 2018)

My postgresql is in a jail and normally won't start at boot or
`service postgresql start`

```
pg_ctl: directory "/var/db/postgres/data10" does not exist
```

so I `ln -s /usr/local/pgsql/data /var/db/postgres/data10` and It now works at boot time.


----------



## idle (Nov 5, 2018)

```
service postgresql initdb
```


----------



## ShelLuser (Nov 5, 2018)

Paul Belair said:


> ```
> pg_ctl: directory "/var/db/postgres/data10" does not exist
> ```
> 
> so I `ln -s /usr/local/pgsql/data /var/db/postgres/data10` and It now works`at boot time.`


Bad idea, and a classic case of misconfiguration. The default data directory for PostgreSQL is /usr/local/pgsql/data. So if your environment expected that in /var/db then I can only assume that you copied your psql instance (and its configuration) from another environment, probably Linux?

Instead you should look into /usr/local/pgsql/data/postgresql.conf and check for the data_directory property which is most likely pointing to the wrong location, change that and your problem will be solved.

Even so, your problem is totally unrelated to that of the OP, also self-inflicted, so there's really not much need to revive a 6 year old thread for this in my opinion.

(edit): obviously I'm ignoring other options for misconfiguration such as /etc/rc.conf (postgresql_data).


----------



## xtaz (Nov 5, 2018)

FYI. The data directory changed with one of the versions of postgres. I can't remember which one specifically. But the later versions like 10 and 11 use /var/db/postgres/data10 for example where 10 is the version number. This was intended so that you could have multiple versions installed at the same time and do easier upgrades from one to the other.


----------

