Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A PostgreSQL Docker container that automatically upgrades your database

github.com

Post reply on HN
Print view View original

71–80 of 93 posts

Re: A PostgreSQL Docker container that automatically upgrades your database

#71

Post by KingOfCoders »

ekidd wrote: post #62
neilv wrote: post #54

> It's whole purpose in life is to automatically detect the version of PostgreSQL used in the existing PostgreSQL data directory, and automatically upgrade it (if needed) to the latest version of PostgreSQL. In a small startup... * If the data is mission-critical and constantly changing, PostgreSQL is a rare infra thing for which I'd use a managed service like AWS RDS, rather just Debian Stable EC2 or my own containe…

RDS is very useful for companies which are big enough to employ, say, 2 programmers, but still too small to employ a DBA. The hard part of running a database, in my experience, isn't setting up or running it. The hard part isn't even configuring backups. The hard part is noticing that your backups have been broken for years, before you actually need to restore from them. Yes, yes, you know how to do this correctly. B…

In my startup (until our exit) I've run Postgres on my own, with streaming backups, daily backup tests (automatic restores and checks), offsite backups and (slow) upgrades.

Data wasn't mission critical and customers could live with 5min downtimes for upgrades once a year.

No problem for years. When we had hosted Mongo, we had more problems.

If I have the money I'll use a managed database. But running Postgres in a startup up to $10m ARR / ~1TB data seems not like a problem.

Re: A PostgreSQL Docker container that automatically upgrades your database

#72

Post by justinclift »

justinclift wrote: post #65
rcfox wrote: post #53

Earlier quoted context omitted.

"A PostgreSQL Docker container that automatically upgrades your database" Still uses "upgrade", but highlights the fact that you're changing the artifact and not just the software.

Thanks, that sounds good. Looks like I can't edit the title for this any more now though. I'll email the HN mods and ask them if they can do it. :)

Title changed. :)

Also copied that wording to the GitHub repo page, Docker Hub page, and will probably use it everywhere else that needs a description too. :D

Re: A PostgreSQL Docker container that automatically upgrades your database

#73

Post by justinclift »

justinclift wrote: post #23
justsomehnguy wrote: post #20

mkdir /var/lib/postgresql/data/old mv -v /var/lib/postgresql/data/* /var/lib/postgresql/data/old/ mkdir /var/lib/postgresql/data/new You should create the both dirs first, then check if they do really exist and only then move the files. Shit happens and it's better be safe than sorry. Also I would replace ../old and ../new with $OLD and $NEW to have a bit less clutter in the next block with the explicit upgrade calls…

Cool, good thinking, thanks heaps. I'll implement that in the morning. :)

Implemented in https://github.com/justinclift/docker-pgautoupgrade/commit/0....

I think I got everything right, and it passes the test harness, but please give it a look over if you have a few minutes. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#74

Post by justinclift »

javajosh wrote: post #70
justinclift wrote: post #23

Earlier quoted context omitted.

Cool, good thinking, thanks heaps. I'll implement that in the morning. :)

This looks like this will use twice the disk.

Nah. The concept of verifying that the mkdir's worked is sound and also easy to do. They just need doing individually in the script, as doing both in the same spot would muck up the wildcard a bit later on.

Using variable names better ($OLD, $NEW) is a good idea too. Should cut down any potential typo risk as well. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#75

Post by justsomehnguy »

justinclift wrote: post #74
javajosh wrote: post #70

Earlier quoted context omitted.

This looks like this will use twice the disk.

Nah. The concept of verifying that the mkdir's worked is sound and also easy to do. They just need doing individually in the script, as doing both in the same spot would muck up the wildcard a bit later on. Using variable names better ($OLD, $NEW) is a good idea too. Should cut down any potential typo risk as well. :)

If you prefer to work with the dirs under pgdata (well it makes sense...) you can just make the list of files (maybe even write it to the file, maybe even write a batch file which would move them) and use it for moving the data from pgdata to old. This saves an unnecessary move.

Add: are sure about "${NEW}"/* in this?

  444   mv -v "${NEW}"/* "${PGDATA}"

Re: A PostgreSQL Docker container that automatically upgrades your database

#76

Post by justinclift »

justsomehnguy wrote: post #75
justinclift wrote: post #74

Earlier quoted context omitted.

Nah. The concept of verifying that the mkdir's worked is sound and also easy to do. They just need doing individually in the script, as doing both in the same spot would muck up the wildcard a bit later on. Using variable names better ($OLD, $NEW) is a good idea too. Should cut down any potential typo risk as well. :)

If you prefer to work with the dirs under pgdata (well it makes sense...) you can just make the list of files (maybe even write it to the file, maybe even write a batch file which would move them) and use it for moving the data from pgdata to old. This saves an unnecessary move. Add: are sure about "${NEW}"/* in this? 444 mv -v "${NEW}"/* "${PGDATA}"

Yeah, this syntax looks a bit unwieldy:

    "${NEW}"/*
But it's specifically to do wildcard expansion of the quoted string, and the shell interpreter is happy with it. I'm open to suggestions for improvements though. :)

---

This is confusing to me:

    ... you can just make the list of files (maybe even write it to the file, maybe
    even write a batch file which would move them) and use it for moving the data
    from pgdata to old. This saves an unnecessary move.
I'm not understanding what you're meaning here.

I understand the "make a list of files" bit, but I'm not grokking why doing that is an improvement, and I'm not seeing where there's an unnecessary move that could be eliminated?

The pg_upgrade process is pretty much:

    1. Initialise a fresh data directory using the new PostgreSQL version
    2. Run pg_upgrade, pointing at both the old and new data directories
    3. Start the database using the new data directory
For the "automatic upgrade container" purposes, we need to do everything under the single mount point so the "--link" option to pg_upgrade is effective and uses hard links.

Thus the "move things into an 'old' directory" first, then the "move the converted 'new' data into place" bit afterwards.

Re: A PostgreSQL Docker container that automatically upgrades your database

#77

Post by alphager »

wokkel wrote: post #38

So we come full circle it seems. Apt update/upgrade already does this and has been doing this for a few years now. So if i drop docker and just run a container with Debian I get this with a few years of testing behind it.

This is for the school of "the container is immutable". They don't want to use a years old container and upgrade the binaries inside.

Re: A PostgreSQL Docker container that automatically upgrades your database

#79

Post by sharts »

ekidd wrote: post #62
neilv wrote: post #54

> It's whole purpose in life is to automatically detect the version of PostgreSQL used in the existing PostgreSQL data directory, and automatically upgrade it (if needed) to the latest version of PostgreSQL. In a small startup... * If the data is mission-critical and constantly changing, PostgreSQL is a rare infra thing for which I'd use a managed service like AWS RDS, rather just Debian Stable EC2 or my own containe…

RDS is very useful for companies which are big enough to employ, say, 2 programmers, but still too small to employ a DBA. The hard part of running a database, in my experience, isn't setting up or running it. The hard part isn't even configuring backups. The hard part is noticing that your backups have been broken for years, before you actually need to restore from them. Yes, yes, you know how to do this correctly. B…

While true, you'd think something as important as a DB would be something worthy of creating some kind of nightly automated testing/verification of a restore procedure (or multiple).

Re: A PostgreSQL Docker container that automatically upgrades your database

#80

Post by ssd532 »

KingOfCoders wrote: post #71
ekidd wrote: post #62

Earlier quoted context omitted.

RDS is very useful for companies which are big enough to employ, say, 2 programmers, but still too small to employ a DBA. The hard part of running a database, in my experience, isn't setting up or running it. The hard part isn't even configuring backups. The hard part is noticing that your backups have been broken for years, before you actually need to restore from them. Yes, yes, you know how to do this correctly. B…

In my startup (until our exit) I've run Postgres on my own, with streaming backups, daily backup tests (automatic restores and checks), offsite backups and (slow) upgrades. Data wasn't mission critical and customers could live with 5min downtimes for upgrades once a year. No problem for years. When we had hosted Mongo, we had more problems. If I have the money I'll use a managed database. But running Postgres in a st…

How do you test backup restoration on daily basis?
Post reply on HN