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

PostgreSQL 14

postgresql.org

Post reply on HN
Print view View original

261–270 of 293 posts

Re: PostgreSQL 14

#261

Post by pilif »

zozbot234 wrote: post #47
yarcob wrote: post #38

Earlier quoted context omitted.

Why would you first upgrade to PG 10?

Native logical replication (making it possible to upgrade without downtime) was introduced in pgSQL 10. But if you're going to have downtime anyway, there's no reason not to do the upgrade in a single step. pg_upgrade should support that quite easily.

also, logical replication has some issues that make it not necessarily perfect for cross version migration: schema changes and sequence usages are not synced.

While schema changes are probably not much of a problem because that's something you can easily prevent during migration, sequence usage is because once you fail over to the logically replicated secondary, you will have to reset all sequences to their current values or all further inserts into tables with sequences will fail.

the other option, of course is to not use sequences but rather use UUIDs for your primary keys, but those have their other collection of issues

Re: PostgreSQL 14

#262

Post by sudhirj »

Diggsey wrote: post #250
sudhirj wrote: post #27

Earlier quoted context omitted.

With downtime, I guess the pg_upgrade tool works fine. Without downtime / near-zero downtime is more interesting though. Since this is an old version, something like Bucardo maybe? It can keep another pg14 instance in sync with your old instance by copying the data over and keeping it in sync. Then you switch your app over to the new DB and kill the old one. Newer versions make this even easier with logical replicati…

pg_upgrade has a `link` option that when used reduces the time take to ~15 seconds. IMO, that counts as "near-zero downtime".

Sort of, yeah. I usually consider zero-downtime to be ‘imperceptible to users’. This option is interesting, though, first I’ve heard of it. Thanks!

Re: PostgreSQL 14

#263

Post by bfelbo »

mixmastamyk wrote: post #252
bfelbo wrote: post #246

Earlier quoted context omitted.

There’s plenty of GUI tools, but what about tools for writing SQL inside VS Code? We usually write our DB migrations in VS Code along with any other code changes, but the PG support in VS Code seems to be lacking. Just some naïve validation of PostgreSQL code inside VS Code would be awesome!

I used pgsanity at the CLI for a while when I unfortunately had coding and testing split between two machines. Can be used with any editor. https://github.com/markdrago/pgsanity

Interesting, thanks! I wonder how easy it would be to write a VS Code plug-in using that.

Re: PostgreSQL 14

#264

Post by GrayShade »

dylanz wrote: post #101
buro9 wrote: post #23

Suppose I had a "friend" with a PostgreSQL 9.6 instance (a large single node)... what's the best way to upgrade to PostgreSQL 14?

If you're on a single node, probably something like this. This is what my "friend" is doing later this week. He's migrating from 9.5 to 13: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt install postgresql-13 sudo servi…

I think you can use -o '-D /etc/postgresql/9.5/main' -O '-D /etc/postgresql/13/main' to avoid making the symlink to postgresql.conf. This should also make it easier if postgres.conf tries to includes files from a conf.d subdirectory.

Re: PostgreSQL 14

#265

Post by nix23 »

dijit wrote: post #223
nix23 wrote: post #210

Earlier quoted context omitted.

Copy/hardlink the data-files and start the new engine instead have to make a dump and import it? It's a joke that you have to make a offline dump and import for upgrades with pgsql.

Did you miss this? https://www.postgresql.org/docs/10/pgupgrade.html

Works one out of three without hiccups.

Re: PostgreSQL 14

#266

Post by olavgg »

Tostino wrote: post #150
olavgg wrote: post #61

Earlier quoted context omitted.

This is one of the major pain points with PostgreSQL. Unless you absolutely need any new features and uptime is important, you can just continue using PostgreSQL 9.6 even thought it is EOL. https://www.postgresql.org/support/versioning/ It will most likely work great for many more years. I wish future versions of PostgreSQL will have some backwards compatibility for old system/data tables/datastructures and be able t…

This is not really true for a number of reasons. pg_upgrade is absolutely the preferred method if you are able to spare small downtime. It will absolutely work to upgrade your existing data structures to be compatible with the new Postgres version, so not sure what you even mean. It doesn't automatically re-create indexes (to take advantage of new features) for example, but that is likely something you don't want to…

If you read the link about pg_upgrade, you're not guaranteed that pg_upgrade will work if the underlying data storage format changes. pg_upgrade may not work in a future version of PostgreSQL. It works for version 14 though.

Re: PostgreSQL 14

#267

Post by pabs3 »

throw0101a wrote: post #239
idoubtit wrote: post #187

Earlier quoted context omitted.

Putting the key in /etc/apt/trusted.gpg.d/ gives it too much power over all the repositories. A malicious (maybe compromised) third party repository could publish a package that replace an official Debian package. So trusted.gpg.d/ is not the recommended method. For more information, see the official Debian wiki which states "The key MUST NOT be placed in /etc/apt/trusted.gpg.d" https://wiki.debian.org/DebianReposito…

Actually it states: > The key MUST NOT be placed in /etc/apt/trusted.gpg.d or loaded by apt-key add. And yet the snippet that I quoted has the latter command. Further: the link has the example " [signed-by=/usr/share/keyrings/deriv-archive-keyring.gpg] ". Perhaps it's my BSD upbringing showing through, but shouldn't only/mostly OS-provided stuff generally live in /usr/share? Shouldn't locally-added stuff go into /usr…

The idea is you put the keyring into the same place where the -keyring package will be installed, so the -keyring package will overwrite it, so you don't then have a temporary keyring you downloaded with wget to delete, because it was overwritten by the -keyring package.

Re: PostgreSQL 14

#268

Post by pabs3 »

idoubtit wrote: post #187
throw0101a wrote: post #149

Earlier quoted context omitted.

> wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Is there a reason to do this rather than putting the key in /etc/apt/trusted.gpg.d/?

Putting the key in /etc/apt/trusted.gpg.d/ gives it too much power over all the repositories. A malicious (maybe compromised) third party repository could publish a package that replace an official Debian package. So trusted.gpg.d/ is not the recommended method. For more information, see the official Debian wiki which states "The key MUST NOT be placed in /etc/apt/trusted.gpg.d" https://wiki.debian.org/DebianReposito…

Unless you are auditing all third-party packages before installing them, any package can modify /etc/apt/trusted.gpg.d/ by dropping a file in there, or from its postinst/etc scripts at installation time etc. So using the signed-by mechanism isn't much of a security enhancement without the auditing.

Re: PostgreSQL 14

#269

Post by dijit »

nix23 wrote: post #265
dijit wrote: post #223

Earlier quoted context omitted.

Did you miss this? https://www.postgresql.org/docs/10/pgupgrade.html

Works one out of three without hiccups.

Sounds like a fun bug then.

I’ve never had it fail for me. :/

Re: PostgreSQL 14

#270

Post by ewgwegg »

Disappointed by the release. No big changes. Still using processes instead of threads for connections. No build-in sharding/high availability (like Sql Server Always On Availability Group). No good way to pass session variables to triggers (like username). No scheduled tasks like in MySql. Temporal tables are still not supported 10 years after the spec. is ready.
Post reply on HN