GoWiki | openbsd | Postgres

# Postgres

## Install

```
pkg_add postgresql-server

su - _postgresql
initdb -U postgres --encoding=UTF-8 --locale=en_EN.UTF-8 -D /var/postgresql/data

sed -i 's/max_connections = 20/max_connections = 100/' /var/postgresql/data/postgresql.conf
sysctl -q kern.seminfo.semmns=160
echo "kern.seminfo.semmns=160" >>/etc/sysctl.conf

rcctl enable postgresql
rcctl start postgresql
```

## Upgrade

Install new packages:

```
pkg_add -ui postgresql-server
pkg_add postgresql-pg_upgrade-13.2p1
```

Prepare:
```
rcctl stop postgresql
mv /var/postgresql/data /var/postgresql/data.old
```

Upgrade:

```
su - _postgresql
initdb -U postgres --encoding=UTF-8 --locale=en_EN.UTF-8 -D /var/postgresql/data
pg_upgrade \
  -b /usr/local/bin/postgresql-12 \
  -B /usr/local/bin \
  -d /var/postgresql/data.old \
  -D /var/postgresql/data \
  -U postgres \
  --check
pg_upgrade \
  -b /usr/local/bin/postgresql-12 \
  -B /usr/local/bin \
  -d /var/postgresql/data.old \
  -D /var/postgresql/data \
  -U postgres \
  --link
```

Start new postgres:

```
rcctl start postgresql
vacuumdb -U postgres --all --analyze-only
```