A PostgreSQL Docker container that automatically upgrades your database

Post by Alifatisk » Mon, Jul 17, 2023, 11:00 AM UTC

johannes1234321 wrote: post #9

Alifatisk wrote: post #5

I have always wondered how a database and its persistence is handled in a containerized environment. This covers one of the issues.

The way you handle persistence is by using storage volumes mounted form the outside. Don't put the data in the container, only the software, which then can be replaced. Then managing is similar to other environments. For updating you don't replace rpm/deb packages and restart, but replace the container using the same volume, which should trigger the dbms-specific update routine. Similarly you do backups similar to ou…

Post by neilv » Mon, Jul 17, 2023, 2:33 PM UTC

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…

Post by kevinventullo » Mon, Jul 17, 2023, 3:59 PM UTC

amluto wrote: post #61

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…

I tried to move a production MySQL database to RDS. Hours later, I decided that I had wasted hours on a crappy product, and I gave up. RDS, at least for MySQL, is, in my opinion, crap.

Post by freedomben » Mon, Jul 17, 2023, 11:14 PM UTC

tempest_ wrote: post #22

johannes1234321 wrote: post #11

Earlier quoted context omitted.

> I've not done stuff with kubernetes yet though, so I have no idea how it's done there. Essentially the same, except that K8s gives you a wide variety of storage backend integrations (Storage classes + storage providers) which can attach "anything" (local volumes on the node, NFS, NAS, Cloud Volumes, ...) depending on your local environment and needs.

Are people putting their databases in K8s now? I thought the old rule of thumb was don't do that but perhaps it has changed. Feels like the database would take a massive performance hit using network backed storage unless the software is aware of that fact.

Post by justsomehnguy » Tue, Jul 18, 2023, 9:33 AM UTC

justinclift wrote: post #76

justsomehnguy wrote: post #75

Earlier quoted context omitted.

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 ol…

    $files = gci $PGDATA
    try {
        New-item "$PGDATA/old" -erroraction stop
        New-item "$PGDATA/new" -erroraction stop
        }
    catch {
        throw "Failed to create the necessary dirs"
        }
    try {
        $files | Move-Item -Destination "$PGDATA/old" -erroraction stop
        }
    catch {
        # throw "Failed to move pg_data, your databases are now borked, good luck"
        gci "$PGDATA/new" | move-item $PGDATA
        }