34

I had postgres 11 installed using docker-compose. I wanted to upgrade it to 12 but even though I have removed the container and its volume but the status of the container says "Restarting".

Here is my docker-compose file

version: '3.5'
services:
  postgres:
    image: postgres:12
    environment:
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
    - "5432"
    restart: always
    volumes:
    - /etc/postgresql/12/postgresql.conf:/var/lib/postgresql/data/postgresql.conf
    - db_data:/var/lib/postgresql/data
volumes:
  db_data:

However it is not working and the logs has the following issue

2020-07-02T12:54:47.012973448Z The files belonging to this database system will be owned by user "postgres".
2020-07-02T12:54:47.013030445Z This user must also own the server process.
2020-07-02T12:54:47.013068962Z 
2020-07-02T12:54:47.013222608Z The database cluster will be initialized with locale "en_US.utf8".
2020-07-02T12:54:47.013261425Z The default database encoding has accordingly been set to "UTF8".
2020-07-02T12:54:47.013281815Z The default text search configuration will be set to "english".
2020-07-02T12:54:47.013293326Z 
2020-07-02T12:54:47.013303793Z Data page checksums are disabled.
2020-07-02T12:54:47.013313919Z 
2020-07-02T12:54:47.013450079Z initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
2020-07-02T12:54:47.013487706Z If you want to create a new database system, either remove or empty
2020-07-02T12:54:47.013501126Z the directory "/var/lib/postgresql/data" or run initdb
2020-07-02T12:54:47.013512379Z with an argument other than "/var/lib/postgresql/data".

How could I remove or empty this /var/lib/postgresql/data when the container is constantly restarting?

Thanks in advance

Zoe
  • 27,060
  • 21
  • 118
  • 148
Kashif
  • 495
  • 1
  • 5
  • 16
  • 1
    Upgrading from Postgres 11 --> 12 is a major upgrade and you can't just substitute one for the other. If the 11 instance has data associated with it you will need to install the 12 instance in another location and either do a dump/restore or use pg_upgrade. – Adrian Klaver Jul 02 '20 at 13:58
  • Future users: Please check for misconfigurations too. For me, it was a typo in yml file. – Amir M Jan 02 '21 at 07:23
  • Have a look at https://stackoverflow.com/questions/35679995/how-to-use-a-postgresql-container-with-existing-data – steve Feb 07 '21 at 00:09

6 Answers6

36

Quoting @yosifkit from this issue

The volume needs to be empty or a valid already initialized postgres database with the file PG_VERSION in there so the init can be skipped.

... If there are any files or folders in there like lost+found it will probably fail to initialize. If there are files that you want to keep in the volume (or have no control over) you could adjust the PGDATA environment variable to point to a sub-directory in there like -e PGDATA=/var/lib/postgresql/data/db-files/.

So I added PGDATA to the environment section of the compose file to solve the issue (notice the some_name at the end):

services:
  postgres:
    image: postgres:12
    environment:
      PGDATA: /var/lib/postgresql/data/some_name/
pmsoltani
  • 976
  • 8
  • 13
15

I got this issue because the /var/lib/postgresql/data/postgresql.conf and the /var/lib/postgresql/data overlap in the docker container at /var/lib/postgresql/.

An example of a broken config is:

version: "3.8"
services:
  db:
    image: "postgres:10"
    ports:
      - "5432:5432"
    volumes:
      - ./postgresql.conf:/var/lib/postgresql/data/postgresql.conf
      - ./pg-data:/var/lib/postgresql/data

To avoid this I tell PostgreSQL to find it's config in /etc/postgresql.conf instead so the overlapping volumes don't occur like this:

version: "3.8"
services:
  db:
    image: "postgres:10"
    command: ["postgres", "-c", "config_file=/etc/postgresql.conf"]
    ports:
      - "5432:5432"
    volumes:
      - ./postgresql.conf:/etc/postgresql.conf
      - ./pg-data:/var/lib/postgresql/data

This is similar to what pmsoltani suggests, but I move the location of the postgresql.conf file instead of the data directory.

rjdkolb
  • 10,377
  • 11
  • 69
  • 89
3

I had the same issue today, I fixed it by removing the content of the volume db_data in your case

docker volume ls
docker volume inspect db_data <-- will show you the mountpoint

I went to the directory (mountpoint) ex: /path/data

cp data data.backup
cd data
rm -R *

And start services:

docker-compose up -d
Mraimou
  • 175
  • 3
  • 13
1

It pops up as the first search result without approved answer, so let me add my solution here.

I want to provide custom postgresql.conf and pg_hba.conf in /var/lib/postgresql/data where the files are stored by default in image postgres:latest (PostgreSQL 15 as of the time of writing).

There is a directory provided by the image where you can store .sql or .sh files to initialize the database: /docker-entrypoint-initdb.d/.

I COPY crafted postgresql.conf and pg_hba.conf files to /tmp and COPY a short Bash script that will move the configuration files into /var/lib/postgresql/data. It ensures proper order of invocation: first initdb, and then replacement of config files.

noneo
  • 129
  • 6
0

I solved it by providing a sub path

      volumeMounts:
        - mountPath: /var/lib/postgresql/data
          name: some-name
          subPath: data
sartysam
  • 23
  • 5
-7

Edit: Just use this for development environment, otherwise you will lose your data.

Another solution is to simply remove the volume attached to the container:

docker-compose down -v
Mehrdad Salimi
  • 1,400
  • 4
  • 16
  • 31
nicocharlery
  • 9
  • 2