Downgrading postgresql database

[Alain De Vos]

I want to move a few postgresql databases from postgresql13 to postgresql12.
I think I install postgresql13 in a jail.
But what do i do next ?

 

[eternal_noob]

Does exporting the data from 13 and importing it in 12 not work? Any errors?
And the big question is: WHY?

 

[Alain De Vos]

timescaledb seems to have an annoying depedency on postgresql12-server.
I'll first give pg_upgrade a try.
But pg_upgrade returned ... this utility can only upgrade

 

[mark_j]

There's no real tool provided by postgres to do this because forward compatibility is not a 'thing' with postgres as things change; sometimes a lot. If your database is simple, then a pg_dump followed by an import into a new v12 should get you 99% of the way.

 

[Alain De Vos]

This is what i will try:
-reinstall postgresql13-server
-perform a pg_dumpall
-reinstall postgresql12-server
-Import the sql dump

 

[mark_j]

I was going to suggest a build from ports to get around this because whatever specific dependency (if any) to v12 of PostgreSQL databases/timescaledb has would be in v13 unless it was a regression.
Your current approach of backup/restore would probably be easier and quicker, though.

 

[ct85711]

Looking through Timescale's Docs; they say that PostgreSQL 13 is supported since 2.1.0 (Explicitly says that support was added). Not assuming which version you need to use, but maybe this may give you something to go with.
https://docs.timescale.com/timescaledb/latest/overview/release-notes/#v2-1-0-2021-02-22

 

[Alain De Vos]

The freebsd port is lagging, ie still depends on on postgresql12,

FreshPorts -- databases/timescaledb: Time-series database built on PostgreSQL

An open-source time-series database optimized for fast ingest and complex queries built on PostgreSQL.

www.freshports.org

But i'll give this git repository a try,

timescaledb/docs/BuildSource.md at main · timescale/timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension. - timescale/timescaledb

github.com

 

[ct85711]

The freebsd port is lagging, ie still depends on on postgresql12

I've encountered this problem until I figured out what was going on; freshports is listing only 1 version (usually the oldest) that is supported. Looking at the makefile for it the git tree, it lists either 12 or 13 as acceptable versions.

 

[Alain De Vos]

Interesting , in that case i change my make.conf:

DEFAULT_VERSIONS+=pgsq=l13

when building postgresql & timescaledb.
I'll give that a try

 

[SirDice]

freshports is listing only 1 version (usually the oldest) that is supported.

Freshports shows the version that's set as default. And that's 12. See /usr/ports/Mk/bsd.default-versions.mk:

# Possible values: 9.6, 10, 11, 12, 13
PGSQL_DEFAULT?=		12

DEFAULT_VERSIONS+=pgsql13

You're missing an = here. That should be

DEFAULT_VERSIONS+= pgsql=13

 

[Alain De Vos]

Everything just compiled fine with postgresql13.
I was a bit confused by the information given on freshports.