usage with the official postgresql images with a single data volume

[lanrat]

The official docker library PostgreSQL image uses a single directory/volume to store all of its configuration and data /var/lib/postgresql/data and does not use a version folder like your scripts seem to want ex: /var/lib/postgresql/9.5/data.

Is there a way to use this tool with the folder layout presented by the PostgreSQL image from the docker library, without having to move everything between two different volumes?

[ecbrodie]

@tianon I am running into this exact issue too. What are the steps for someone to upgrade the Postgres version of the data from the non-versioned volume path? Thank you.

[tianon]

Not sure how I missed this, sorry!

I think the README covers this case:

For this to be feasible, your directory structure should look something like this: (if yours does not, either adjust it, or scroll down to see the alternate pattern for running this image)

...

If your two directories (denoted below as PGDATAOLD and PGDATANEW) do not follow this structure, then the following may also be used (but will be slower):

$ docker run --rm \
	-v PGDATAOLD:/var/lib/postgresql/OLD/data \
	-v PGDATANEW:/var/lib/postgresql/NEW/data \
	tianon/postgres-upgrade:OLD-to-NEW

...

(Which should work equally well with bind mounts and volumes.)

[ecbrodie]

Thanks for the note @tianon. I tried following this part of the README exactly, but still ran into issues. I'll walk through step-by-step what I did, so hopefully we can identify the error in my approach or a possibly improvement to this project.

• Begin with a project that uses a Postgres 9.4 database, deployed to a local dev environment as a container deployed through docker-compose. My abbreviated docker-compose.yml file looks like so (irrelevant parts of the file left out, but I can add more information as requested):

version: '3'

services:
  db:
    container_name: my_db
    image: "postgres:9.4"
    ports:
      - "15432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data

volumes:
  postgres-data:

• Launch the db container with docker-compose up -d db
• Insert some data into the database. For my Rails project, that's as simple as rake db:setup
• Run the following command, to execute the docker-postgres-upgradecontainer to upgrade my project from Postgres 9.4 to 9.6:

docker run --rm \
        -v postgres-data:/var/lib/postgresql/data \
        -v postgres-data-9.6:/var/lib/postgresql/9.6/data \
        tianon/postgres-upgrade:9.4-to-9.6

• The following output is produced:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/9.6/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/9.6/data -l logfile start


check for "/var/lib/postgresql/9.4/data/base" failed: No such file or directory

Failure, exiting

My initial guess is that the script was assuming that my OLD data volume was following the directory path structure was following your recommended approach (version-based directory names), since it was unable to find the 9.4-based directory name but my project is currently using a general directory name. I am also assuming that the values I pass in the -v flags in the docker run command are supposed to point to docker volume names and not absolute file paths on the host machine.

Anyway, I'd love to hear your input on my approach. I am prepared to that EUREKA moment when I realize I forgot about some critical knowledge about Docker fundamentals. But, I'm also willing to help out on a PR if one is needed.

Thank you.

[tianon]

Try this instead:

docker run --rm \
        -v postgres-data:/var/lib/postgresql/9.4/data \
        -v postgres-data-9.6:/var/lib/postgresql/9.6/data \
        tianon/postgres-upgrade:9.4-to-9.6

[ecbrodie]

Ah okay. I overlooked that the part to the right of the : character was the file path on the VM for the docker-postgres-upgrade container VM. After running this, the upgrade was successful, thank you.

I am running into a new type of error when I try to access my database in my application. Are you aware of what might be going on here? If this is out of scope of this project, that's okay.

PG::ConnectionBad: FATAL:  no pg_hba.conf entry for host "172.18.0.1", user "my_user", database "my_database", SSL off

[tianon]

Sounds like "pg_hba.conf" isn't right -- if you "docker exec" into the container, you should be able to verify that the contents include the correct auth lines necessary for your user to connect properly.

[ecbrodie]

@tianon These are the relevant lines in my pg_hba.conf directory on the new container (everything else are just comments):

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

I'm not an expert on this file, but it looks like it should be accepting all connections to me. Perhaps should there have been a update applied to this file by pg_upgrade, but that failed? Something else I need to do? The no pg_hba.conf entry for host "172.18.0.1" error message is 100% reproducible.

[tianon]

There should be a line something like "host all all all md5" or "host all all all trust" (if you're using passwordless auth -- not recommended) which was set up by our docker-entrypoint during the initial database initialization. Perhaps "pg_upgrade" didn't copy that over properly? Regardless, adding "host all all all md5" will probably fix it.

[ecbrodie]

No, that didn't work unfortunately. After I edited the pg_hba.conf and restarted the database, I ran into connection issues, the IP address and port was not responding.

I am wondering whether my usage of named volumes is causing an issue. The examples in the README are using absolute paths to the host machine. Perhaps this project is meant to be used with bind mounts instead of named volumes? (https://docs.docker.com/engine/admin/volumes/)

[tianon]

Named volumes are just bind mounts that Docker manages the source directory for you. 😞 😕

[ecbrodie]

Okay, then that concern is obviously invalid and thus not the right path for me to dig deeper into.

Okay, I'll have to keep trying on my end and fiddling around with Postgres files. But your help with getting this docker container to execute properly on my system was useful. Thank you.

[lanrat]

@tianon Is there the possibility of making the migration with with the folder structure in the the default postgresql image but still get the speed benefits of separate version folders?

I have a VERY large database so this will make a huge difference.

[tianon]

@lanrat sure, just set PGDATAOLD to the path to the old directory (see https://www.postgresql.org/docs/9.6/static/pgupgrade.html, which this is a very thin wrapper around)

[jbcpollak]

@ecbrodie - got a team of engineers here running into the same problem, we seem to be missing lines from pg_hba.conf. Did you ever solve this problem?

[liukun]

@jbcpollak You can compare pg_hba.conf with the old one.