could not link file “pg_xlog/xlogtemp.20” to “pg_xlog/000000010000000000000001” (initialization of log file): Operation not permitted

I am doing a Docker training session and the container uses PostgreSQL for database needs. On doing docker-compose up, i got the error in the title.

postgres_1 |
postgres_1 | Data page checksums are disabled.
postgres_1 |
postgres_1 | fixing permissions on existing directory /var/lib/postgresql/data … ok
postgres_1 | creating subdirectories … ok
postgres_1 | selecting default max_connections … 100
postgres_1 | selecting default shared_buffers … 128MB
postgres_1 | selecting dynamic shared memory implementation … posix
postgres_1 | creating configuration files … ok
postgres_1 | creating template1 database in /var/lib/postgresql/data/base/1 … LOG: could not link file “pg_xlog/xlogtemp.20” to “pg_xlog/000000010000000000000001” (initialization of log file): Operation not permitted
postgres_1 | FATAL: could not open file “pg_xlog/000000010000000000000001”: No such file or directory
postgres_1 | child process exited with exit code 1

As i use Docker Toolbox, apparently if you create the volume in Windows, there will be permission issues that can’t be fixed.

A relatively easy solution is to create the volume through Docker:

docker volume create psql

then grab the path:

docker volume inspect psql
[
{
“CreatedAt”: “2019-07-09T09:14:01Z”,
“Driver”: “local”,
“Labels”: {},
“Mountpoint”: “/mnt/sda1/var/lib/docker/volumes/psql/_data”,
“Name”: “psql”,
“Options”: {},
“Scope”: “local”
}
]

And then use that path in your docker-compose.yml file:

postgres:
image: postgres:9.4.5
environment:
POSTGRES_USER: mobydock
POSTGRES_PASSWORD: yourpassword
ports:
– ‘5432:5432’
volumes:
– /mnt/sda1/var/lib/docker/volumes/psql/_data:/var/lib/postgresql/data

After this change, everything will work like a charm.

PostgreSQL – useful stuff

(Everything below has been done on FreeBSD 10.1 with PostgreSQL 9.3)

How to connect to the PostgreSQL database:

su – psql

pgsql -U pgsql -d template1

How to list the PostgreSQL databases

\l #(that is a lowercase L not an i)

How to add a user and grant permissions to a certain PostgreSQL database:

psql -U pgsql -d template1

CREATE USER username WITH PASSWORD ‘password’;

CREATE DATABASE dbname;

GRANT ALL PRIVILEGES ON DATABASE dbname to username;

Remove database and user:

DROP DATABASE dbname;

DROP USER username;

Backup/Dump | Restore a PostgreSQL database:

dump -> pg_dump -U username dbname -f dump.sql

restore -> psql -U username -d dbname -f dump.sql