28fa445143
Co-authored-by: Anders Gustafsson <anders@photowall.se> Co-authored-by: Arwid Thornström <arwidt@gmail.com> Co-authored-by: Fredrik Ringqvist <fredrik.ringqvist@photowall.se>
99 lines
2.3 KiB
Markdown
99 lines
2.3 KiB
Markdown
# Database migration
|
|
|
|
[Detailed information about this](http://ian-shafer.github.io/2016/02/02/db-migrations/)
|
|
|
|
## Setup
|
|
|
|
#### Configuration
|
|
The credentials for each environment that could be updated is stored in the
|
|
`env/` directory.
|
|
|
|
***Example***
|
|
|
|
`env/default.conf` could look like this:
|
|
```
|
|
#!/bin/bash
|
|
export PSQL_OPTS="-h HOST_NAME -U root"
|
|
export DB_NAME=DATABASE_NAME
|
|
```
|
|
|
|
Create one file for each environment you'd be able to migrate.
|
|
|
|
When `db-upgrade` is issued, it takes the first part of any name as argument and
|
|
performs the migration on selected environment.
|
|
|
|
`$ ./db-upgrade localhost` would for example find a file named `localhost.conf`
|
|
and use the credentials inside for the migration.
|
|
|
|
To avoid having to enter password for selected user on each run, just use a
|
|
[.pgpass](http://www.postgresql.org/docs/9.3/static/libpq-pgpass.html) file.
|
|
An example .pgpass file can be found in lastpass with name `pgpass`.
|
|
|
|
Login information for production cluster can be found is lastpass named `production-cluster - Aurora`.
|
|
|
|
`db-upgrade` assumes the `default` environment should be used if it's argument
|
|
is omitted. So, start by creating an initial `default.conf` with credentials for your localhost or some non-critical environment.
|
|
|
|
#### Migration files
|
|
The files in the /migrations directory are changes to the scheme, not the full
|
|
scheme.
|
|
|
|
The naming convention for migration files are:
|
|
- 000.000.sql
|
|
- 000.001.sql
|
|
- 000.002.sql
|
|
- 000.003.sql
|
|
- 000.004.sql
|
|
- ...and so on
|
|
|
|
**Example**
|
|
The first file, `migrations/000.000.sql` may look like this:
|
|
```
|
|
create table users (
|
|
name varchar(32) not null,
|
|
primary key (name)
|
|
);
|
|
```
|
|
|
|
The second file, `migrations/000.001.sql` may look like this:
|
|
```
|
|
alter table users
|
|
add email varchar(256) not null;
|
|
```
|
|
|
|
To do the actual migration, then just run:
|
|
```
|
|
# ./db-upgrade [ENVIRONMENT NAME]
|
|
```
|
|
|
|
**Example docker-compose.yml for postgres**
|
|
|
|
Execute this first:
|
|
```bash
|
|
mkdir data
|
|
chmod o+rwx data
|
|
```
|
|
|
|
```yml
|
|
version: '3.7'
|
|
|
|
services:
|
|
db:
|
|
image: postgres:13
|
|
restart: always
|
|
environment:
|
|
POSTGRES_PASSWORD: postgres
|
|
TZ: 'Europe/Stockholm'
|
|
PGTZ: 'Europe/Stockholm'
|
|
ports:
|
|
- "5432:5432"
|
|
volumes:
|
|
- ./data:/var/lib/postgresql/data
|
|
- /var/run/postgresql:/var/run/postgresql
|
|
|
|
networks:
|
|
default:
|
|
external: true
|
|
name: postgres
|
|
```
|