PostgreSQL 14 • Hacker News Board

Re: PostgreSQL 14

#31

Post by ape4 » Thu, Sep 30, 2021, 1:47 PM UTC

rolobio wrote: post #7

Love the JSONB subscripts! It will be so much easier to remember! I may not even have to reference the docs!

What is JSONB... https://stackoverflow.com/questions/22654170/explanation-of-...

(Don't upvote me, I just googled it)

Re: PostgreSQL 14

#32

Post by mattashii » Thu, Sep 30, 2021, 1:48 PM UTC

my123 wrote: post #21

mattashii wrote: post #6

Earlier quoted context omitted.

I haven't been able to play with it yet. This was partly due to my lack of SQL Server projects, but mostly due to the lack of availability of the supposedly Apache-licenced sources and/or binaries (and not wanting to configure AWS for 'just testing').

It’s currently in a limited preview, not accessible outright to any AWS customer even.

Yep. I'm quite interested when the 'somewhere in 2021' will be, as the last mention of babelfish from an Amazon-employee on the mailing lists was at the end of March and I haven't heard news about the project since.

Re: PostgreSQL 14

#33

Post by nicoburns » Thu, Sep 30, 2021, 1:49 PM UTC

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?

Looks like the best approach might be to use in-place upgrade tool that ships with Postgres to upgrade to v10 (use Postgres v10 to perform this upgrade). From there you'd be able to create a fresh v14 instance and use logical replication to sync over the v10 database. Before briefly stopping writes while you swap over to the v14 database.

EDIT: Looks like pg_upgrade supports directly upgrading multiple major versions. So maybe just use that if you can afford the downtime.

Re: PostgreSQL 14

#34

Post by yarcob » Thu, Sep 30, 2021, 1:51 PM UTC

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?

Depends on whether you can shut down the server for some time or not. If you can live with some downtime, just use pg_upgrade if you install the new server on the same machine: https://www.postgresql.org/docs/current/pgupgrade.html

Re: PostgreSQL 14

#35

Post by thornygreb » Thu, Sep 30, 2021, 1:51 PM UTC

mypastself wrote: post #30

Somewhat related, but does anybody have suggestions for a quality PostgreSQL desktop GUI tool, akin to pgAdmin3? Not pgAdmin 4, whose usability is vastly inferior. DBeaver is adequate, but not really built with Postgres in mind.

I pay for jetbrains datagrip, worth every penny.

Re: PostgreSQL 14

#36

Post by I_am_tiberius » Thu, Sep 30, 2021, 1:52 PM UTC

Anyone know when it will be available on Azure Flexible Server? Also, does anyone know when Flexible Server will leave Preview status?

Re: PostgreSQL 14

#37

Post by darksaints » Thu, Sep 30, 2021, 1:52 PM UTC

hyper_reality wrote: post #18

PostgreSQL is one of the most powerful and reliable pieces of software I've seen run at large scale, major kudos to all the maintainers for the improvements that keep being added. > PostgreSQL 14 extends its performance gains to the vacuuming system, including optimizations for reducing overhead from B-Trees. This release also adds a vacuum "emergency mode" that is designed to prevent transaction ID wraparound Dealin…

What is wrong with using a 64 bit, or even 128 bit transaction id?

Re: PostgreSQL 14

#38

Post by yarcob » Thu, Sep 30, 2021, 1:53 PM UTC

nicoburns wrote: post #33

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?

Looks like the best approach might be to use in-place upgrade tool that ships with Postgres to upgrade to v10 (use Postgres v10 to perform this upgrade). From there you'd be able to create a fresh v14 instance and use logical replication to sync over the v10 database. Before briefly stopping writes while you swap over to the v14 database. EDIT: Looks like pg_upgrade supports directly upgrading multiple major versions…

Why would you first upgrade to PG 10?

Re: PostgreSQL 14

#39

Post by Keyframe » Thu, Sep 30, 2021, 1:55 PM UTC

mypastself wrote: post #30

Somewhat related, but does anybody have suggestions for a quality PostgreSQL desktop GUI tool, akin to pgAdmin3? Not pgAdmin 4, whose usability is vastly inferior. DBeaver is adequate, but not really built with Postgres in mind.

DataGrip is cool.

Re: PostgreSQL 14

#40

Post by yarcob » Thu, Sep 30, 2021, 1:56 PM UTC

sudhirj wrote: post #27

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?

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…

I think I remember a talk where someone manually set up logical replication with triggers and postgres_fdw to upgrade from an old server with zero downtime.