Comments (18)
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
from docker-postgres-upgrade.
from docker-postgres-upgrade.
@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)
from docker-postgres-upgrade.
@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.
from docker-postgres-upgrade.
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.)
from docker-postgres-upgrade.
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-upgrade
container 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.
from docker-postgres-upgrade.
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
from docker-postgres-upgrade.
from docker-postgres-upgrade.
@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.
from docker-postgres-upgrade.
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/)
from docker-postgres-upgrade.
from docker-postgres-upgrade.
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.
from docker-postgres-upgrade.
@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.
from docker-postgres-upgrade.
@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?
from docker-postgres-upgrade.
@jbcpollak You can compare pg_hba.conf
with the old one.
from docker-postgres-upgrade.
Just seeing this now.
@jbcpollak I switched projects last year and thus no longer had to deal with the same problem. On my old project, I was spiking out a postgres upgrade for a Dockerized app. However, I switches gears because we delayed our upgrade.
Anyway, I hope you figured it out on your end over the last year plus. I have nothing left to contribute to this thread.
from docker-postgres-upgrade.
@ecbrodie - we solved this problem a long while ago, sadly I can't remember how. Someone else posted a comment here today saying they had the same issue but it looks like the comment has been deleted. As far as I'm concerned the problem is closed, but I'll ask my co-workers if they remember our solution and to post back here.
from docker-postgres-upgrade.
This one is related to the issue with pg_hba.conf
issue, #16 - so you have to manage the file yourself after an upgrade
from docker-postgres-upgrade.
Related Issues (20)
- Support custom installed username HOT 1
- Upgrading from 14 to 15 failing with `"/var/lib/postgresql/14/data/PG_VERSION": No such file or directory` HOT 5
- Docker image should run pg_upgrade's generated update_extension.sql file when present HOT 2
- support custom username for example "-U my_username" when connecting HOT 6
- Break database made with Alpine-based container HOT 6
- PostgreSQL 16 HOT 3
- TO VERSION 16 HOT 1
- 11 to VERSION 16 REQUEST HOT 1
- Cannot create directory: File exists. HOT 1
- Docker image for ARM64 (aarch64 / Arm Ampere) architecture HOT 1
- "could not create any Unix-domain sockets" HOT 2
- Add ARM Arch Support HOT 1
- Does the operation of `exec gosu postgres "$BASH_SOURCE" "$@"` overlap with `exec "$@"`? HOT 4
- 13-to-15: FATAL: role "postgres" does not exist HOT 2
- How to support PostgreSQL database upgrade with multiple tablespaces HOT 1
- Collation warning
- script to review when username is not postgres - permission issue
- Is there a reason to remove upgrade ability for older versions? HOT 2
- chown fails
- Support for linux/arm64/v8 HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from docker-postgres-upgrade.