AW: how to merge two postgresql server instances into one

[Marco Lechner]

Hi Adrian,

does this clearify the mission:

Recent:
PostgreSQL 11:
  - Db1_foo
  - Db2_bar
  - postgres
  - template1
PostgreSQL 12:
  - Db3_zii
  - Db4_gee
  - postgres
  - template1

Result after Upgrade:
PostgreSQL 14:
  - Db1_foo
  - Db2_bar
  - Db3_zii
  - Db4_gee
  - postgres (not from PG11/PG12)
  - template1 (not from PG11/PG12)

Used extensions: postgis, hstore.
But I see, that another task might be to have user/roles from both PG11 and 
PG12 copied to PG14.


-Ursprüngliche Nachricht-
Von: Adrian Klaver  
Gesendet: Mittwoch, 6. Oktober 2021 17:33
An: Marco Lechner ; pgsql-general@lists.postgresql.org
Betreff: Re: how to merge two postgresql server instances into one

WARNUNG: Diese E-Mail kam von außerhalb der Organisation. Klicken Sie nicht auf 
Links oder öffnen Sie keine Anhänge, es sei denn, Sie kennen den/die 
Absender*in und wissen, dass der Inhalt sicher ist.


On 10/6/21 2:58 AM, Marco Lechner wrote:
> Hi,
>
> how to merge two postgresql server instances into one? I have a
> postgresql-11 and -12 instance on my server and want to upgrade both
> (pg_upgrade?) into one postgresql-14 instance.

The bigger issue will be the merge. Define what that means in this context?

>
> The postgresql-14 instance does not exist yet (fresh install of
> postgresql-14 server packages). Using pg_upgrade to upgrade the larger 
> one and pg_dumpall/pg_restore for the smaller one? Or are there any 
> other nice recommended workflows?
>
> i.A. Dr. Marco Lechner
>
> Leiter Fachgebiet RN 1 │ Head RN 1
>
> --
>
> Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection
>
> Koordination Notfallschutzsysteme │ Coordination Emergency Systems │ 
> RN 1
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: AW: how to merge two postgresql server instances into one

[Heikki Pernu]

I would suggest you setup a new virtual server, LXC container or docker 
instance with the new
version of pg14, pg_dump the old databases(one by one) and then import 
them to the new ones.
That way, you do not have to mess with multiple installations of 
Postgres and can use minimal
extra resources and can have a later(hopefully more secure) underlying 
OS and libraries for the new version.


You may have to do multiple attempts until you get the dumps cleaned of 
non-relevant information

and/or have correct extensions/whatever in place on the target instance.
You can keep the old databases online the whole time and as long as 
possible until you have

the whole process ready.

The feasibility of the actual final migration depends of course on 
whether you need to have the databases
online all the time or is there downtime allowed. Also, the amount of 
data is of importance as well.

(Database restore may take a very long time. )

If you need to keep the databases online all the time but can keep them 
read-only, this process would
still work. I see no practical way of doing this if you need to keep 
them online and allow modifications while
doing the migration. Possibly it could be done by setting up a 
replication via Slony or something similar but

this is very complicated given the differences in versions.

If more downtime is allowed, it might also be possible to directly 
upgrade one of the instances without
dump/restore cycle, but please have a full backup of all the postgresql 
data files and utilities before that.

After upgrade, you could do dump+restore for the other database.


On 7.10.2021 11.19, Marco Lechner wrote:

Hi Adrian,

does this clearify the mission:

Recent:
PostgreSQL 11:
   - Db1_foo
   - Db2_bar
   - postgres
   - template1
PostgreSQL 12:
   - Db3_zii
   - Db4_gee
   - postgres
   - template1

Result after Upgrade:
PostgreSQL 14:
   - Db1_foo
   - Db2_bar
   - Db3_zii
   - Db4_gee
   - postgres (not from PG11/PG12)
   - template1 (not from PG11/PG12)

Used extensions: postgis, hstore.
But I see, that another task might be to have user/roles from both PG11 and 
PG12 copied to PG14.


-Ursprüngliche Nachricht-
Von: Adrian Klaver 
Gesendet: Mittwoch, 6. Oktober 2021 17:33
An: Marco Lechner ; pgsql-general@lists.postgresql.org
Betreff: Re: how to merge two postgresql server instances into one

WARNUNG: Diese E-Mail kam von außerhalb der Organisation. Klicken Sie nicht auf 
Links oder öffnen Sie keine Anhänge, es sei denn, Sie kennen den/die 
Absender*in und wissen, dass der Inhalt sicher ist.


On 10/6/21 2:58 AM, Marco Lechner wrote:

Hi,

how to merge two postgresql server instances into one? I have a
postgresql-11 and -12 instance on my server and want to upgrade both
(pg_upgrade?) into one postgresql-14 instance.

The bigger issue will be the merge. Define what that means in this context?


The postgresql-14 instance does not exist yet (fresh install of
postgresql-14 server packages). Using pg_upgrade to upgrade the larger
one and pg_dumpall/pg_restore for the smaller one? Or are there any
other nice recommended workflows?

i.A. Dr. Marco Lechner

Leiter Fachgebiet RN 1 │ Head RN 1

--

Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection

Koordination Notfallschutzsysteme │ Coordination Emergency Systems │
RN 1



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Misplaced double quotes in error message

[Adrian Klaver]

On 10/6/21 11:17 PM, Thomas Kellerer wrote:

Hello,

consider the following table, query and error message:

 create table t
 (
   "someColumn" int
 );

 select t.someColumn
 from t;


 ERROR: column t.somecolumn does not exist
   Hint: Perhaps you meant to reference the column "t.someColumn".


For someone proficient in SQL it's pretty clear what the error message means,
but the quotes can be misleading for someone who doesn't really understand
what's going on.


FYI, errors are generically double quoted:

ERROR:  invalid input syntax for type integer: "one"

This is not specific to identifier quoting.



I think if the error message quoted the column name the way it should actually
be used, it would make things easier for someone not used to this, e.g.:

   Hint: Perhaps you meant to reference the column t."someColumn".

Because when the user takes the error message literally they might be tempted
to use "t.someColumn" just to be confronted with the same error message again
which then seems even more confusing.








--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Misplaced double quotes in error message

[Sunil Thakur]

It means
Error thrown by Postgres: Hint: Perhaps you meant to reference the column
"t.someColumn".
must be Hint: Perhaps you meant to reference the column t."someColumn".


*Thanks and Regards,Sunil M. K. Thakur*
 *
  *


On Thu, 7 Oct 2021 at 19:09, Adrian Klaver 
wrote:

> On 10/6/21 11:17 PM, Thomas Kellerer wrote:
> > Hello,
> >
> > consider the following table, query and error message:
> >
> >  create table t
> >  (
> >"someColumn" int
> >  );
> >
> >  select t.someColumn
> >  from t;
> >
> >
> >  ERROR: column t.somecolumn does not exist
> >Hint: Perhaps you meant to reference the column "t.someColumn".
> >
> >
> > For someone proficient in SQL it's pretty clear what the error message
> means,
> > but the quotes can be misleading for someone who doesn't really
> understand
> > what's going on.
>
> FYI, errors are generically double quoted:
>
> ERROR:  invalid input syntax for type integer: "one"
>
> This is not specific to identifier quoting.
>
> >
> > I think if the error message quoted the column name the way it should
> actually
> > be used, it would make things easier for someone not used to this, e.g.:
> >
> >Hint: Perhaps you meant to reference the column t."someColumn".
> >
> > Because when the user takes the error message literally they might be
> tempted
> > to use "t.someColumn" just to be confronted with the same error message
> again
> > which then seems even more confusing.
> >
> >
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Re: AW: how to merge two postgresql server instances into one

[Adrian Klaver]

On 10/7/21 1:19 AM, Marco Lechner wrote:

Hi Adrian,

does this clearify the mission:

Recent:
PostgreSQL 11:
   - Db1_foo
   - Db2_bar
   - postgres
   - template1
PostgreSQL 12:
   - Db3_zii
   - Db4_gee
   - postgres
   - template1

Result after Upgrade:
PostgreSQL 14:
   - Db1_foo
   - Db2_bar
   - Db3_zii
   - Db4_gee
   - postgres (not from PG11/PG12)
   - template1 (not from PG11/PG12)

Used extensions: postgis, hstore.


Are you going to be using the same extension versions.
In particular PostGIS?


But I see, that another task might be to have user/roles from both PG11 and 
PG12 copied to PG14.


Something like:

pg_dumpall -g -p  -U postgres -f _globals.sql

run against the 11 & 12 clusters will get you the roles. NOTE: this will 
also get you tablespaces so if those are in use that is a consideration.


Then :

psql -d postgres -U postgres -p <14_port> -f _globals.sql

will restore them to new cluster. If they are repeated the above will 
throw something like:


ERROR:  role "adrian" already exists

for repeated roles but continue on to fill in the roles that don't exist.

As to rest see Heikki Pernu's post.









--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Misplaced double quotes in error message

[Tom Lane]

Adrian Klaver  writes:
> On 10/6/21 11:17 PM, Thomas Kellerer wrote:
>> consider the following table, query and error message:
>> ERROR: column t.somecolumn does not exist
>> Hint: Perhaps you meant to reference the column "t.someColumn".
>> 
>> For someone proficient in SQL it's pretty clear what the error message means,
>> but the quotes can be misleading for someone who doesn't really understand
>> what's going on.

> FYI, errors are generically double quoted:
> ERROR:  invalid input syntax for type integer: "one"
> This is not specific to identifier quoting.

Yeah.  This is not as simple as it looks, because per our message
style guidelines, double quotes are used to set off inserted text,
independently of whether it is a SQL identifier or something else.
(There is a style violation in this message: the occurrence of
t.somecolumn in the primary message should've been quoted too.)

In translated error messages, the English double quotes are replaced
with whatever the common quoting marks are in that language.  So
for instance in French this becomes

ASTUCE :  Peut-être que vous souhaitiez référencer la colonne « t.someColumn ».

where it's at least clearer that the set-off marks are not meant to be
copied into a SQL statement.

In short, what we've got here is unfortunate confusion between the meaning
of double quotes in ordinary English and their meaning in SQL.  People
complain about this topic every so often, but I've not yet seen a proposal
that would improve matters.

regards, tom lane

Re: DELETE ... USING LATERAL

[Nikhil Benesch]

On Mon, Oct 4, 2021 at 3:21 PM Tom Lane  wrote:
> Not sure what to tell you about the state of the idea that the
> target table could be re-specified in FROM/USING.  I'm hesitant
> to close the door on it permanently, because people do periodically
> wish to be able to left-join the target to something else.  But
> the fact that no one's done anything about it for years suggests
> that it's not that high on anyone's wish list.

Makes sense. Thanks for the insight. Sounds like the status quo is just fine.

Nikhil

Re: Timestamp with vs without time zone.

[Bruce Momjian]

On Tue, Sep 21, 2021 at 05:49:21PM -0400, Tom Lane wrote:
> I think there is plenty of application for timestamps that actually
> include (civil) time zones.  Calendaring, for example.  If I make an
> appointment to see a friend at 2PM some months from now, it's
> understood that that's in the local time zone; if some lawmakers take
> it on themselves to fool with the DST rules before then, we're still
> going to meet at 2PM local time.  And it'd be useful to know whether
> that now conflicts with appointments defined by reference to some
> other zone, so the easy way of "assume it's all local time" doesn't
> cut it.

I wrote a blog about this:

https://momjian.us/main/blogs/pgblog/2017.html#September_27_2017

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

[Gavin Roy]

Hi All,

My team was testing against Postgres 14 to ensure we could cleanly upgrade
and we ran across a regression in our PL/PGSQL code related to the updates
to RETURN QUERY.

Our code which works in previous versions of Postgres uses UPDATE RETURNING
and INSERT RETURNING in combination with RETURN QUERY. It appears that in
the parallelism updates, RETURN QUERY now only accepts SELECT queries.

Was this an intentional change in behavior? We can easily refactor our
PL/PGSQL functions to deal with the change, but if it was intentional,
perhaps it should be documented.

Regards,

Gavin

Re: Currently running queries with actual arguments?

[Bruce Momjian]

On Thu, Sep 23, 2021 at 08:51:49PM +0400, rihad wrote:
> On 9/23/21 7:58 PM, Garfield Lewis wrote:
> > The way I normally get this info is by setting the following:
> > 
> > log_statement = 'all'
> > 
> > then the arguments will be printed in the postgres.log file. There could be 
> > some other way but that is what I know.
> > 
> 
> Thanks, those queries are logged after their completion, but I specifically
> need the list of unfinished long queries currently being executed.

Uh, log_statement prints them before they are executed ---
log_min_duration_statment prints them after execution.  You can verify
this by running:

SET log_statement = 'all';
SELECT pg_sleep(430);

and then looking in the log file.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

[Adrian Klaver]

On 10/7/21 11:38 AM, Gavin Roy wrote:

Hi All,

My team was testing against Postgres 14 to ensure we could cleanly 
upgrade and we ran across a regression in our PL/PGSQL code related to 
the updates to RETURN QUERY.


Our code which works in previous versions of Postgres uses UPDATE 
RETURNING and INSERT RETURNING in combination with RETURN QUERY. It 
appears that in the parallelism updates, RETURN QUERY now only accepts 
SELECT queries.


I'm pretty sure folks are going to want to see an example of the code 
and the errors thrown in version 14.




Was this an intentional change in behavior? We can easily refactor our 
PL/PGSQL functions to deal with the change, but if it was intentional, 
perhaps it should be documented.


Regards,

Gavin



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

[Tom Lane]

Gavin Roy  writes:
> Our code which works in previous versions of Postgres uses UPDATE RETURNING
> and INSERT RETURNING in combination with RETURN QUERY. It appears that in
> the parallelism updates, RETURN QUERY now only accepts SELECT queries.

Yeah, that's a mistake, previously reported and fixed at

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e0eba586b

regards, tom lane

Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

[Gavin Roy]

On Thu, Oct 7, 2021 at 2:54 PM Adrian Klaver 
wrote:

> On 10/7/21 11:38 AM, Gavin Roy wrote:
> > Hi All,
> >
> > My team was testing against Postgres 14 to ensure we could cleanly
> > upgrade and we ran across a regression in our PL/PGSQL code related to
> > the updates to RETURN QUERY.
> >
> > Our code which works in previous versions of Postgres uses UPDATE
> > RETURNING and INSERT RETURNING in combination with RETURN QUERY. It
> > appears that in the parallelism updates, RETURN QUERY now only accepts
> > SELECT queries.
>
> I'm pretty sure folks are going to want to see an example of the code
> and the errors thrown in version 14.
>

Sorry, I thought that was pretty clear. As an example, this worked prior to
14 and no longer works:

CREATE TABLE foo (
  bar  SERIAL  PRIMARY KEY,
  baz  TEXT
);

CREATE FUNCTION update_foo(in_bar INT4, in_baz TEXT) RETURNS SETOF foo AS $$
BEGIN
  RETURN QUERY UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING
bar, baz;
END;
$$ LANGUAGE PLPGSQL;

postgres=# SELECT * FROM update_foo(1, 'baz?');
ERROR:  query is not a SELECT
CONTEXT:  query: UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING
bar, baz
PL/pgSQL function update_foo(integer,text) line 3 at RETURN QUERY

Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

[Gavin Roy]

Thanks so much Tom!

Regards,

Gavin

On Thu, Oct 7, 2021 at 3:05 PM Tom Lane  wrote:

> Gavin Roy  writes:
> > Our code which works in previous versions of Postgres uses UPDATE
> RETURNING
> > and INSERT RETURNING in combination with RETURN QUERY. It appears that in
> > the parallelism updates, RETURN QUERY now only accepts SELECT queries.
>
> Yeah, that's a mistake, previously reported and fixed at
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e0eba586b
>
> regards, tom lane
>

Re: Misplaced double quotes in error message

[Peter J. Holzer]

On 2021-10-07 10:55:09 -0400, Tom Lane wrote:
> Yeah.  This is not as simple as it looks, because per our message
> style guidelines, double quotes are used to set off inserted text,
> independently of whether it is a SQL identifier or something else.
> (There is a style violation in this message: the occurrence of
> t.somecolumn in the primary message should've been quoted too.)
> 
> In translated error messages, the English double quotes are replaced
> with whatever the common quoting marks are in that language.  So
> for instance in French this becomes
> 
> ASTUCE :  Peut-être que vous souhaitiez référencer la colonne « t.someColumn 
> ».
> 
> where it's at least clearer that the set-off marks are not meant to be
> copied into a SQL statement.
> 
> In short, what we've got here is unfortunate confusion between the meaning
> of double quotes in ordinary English and their meaning in SQL.  People
> complain about this topic every so often, but I've not yet seen a proposal
> that would improve matters.

You could use proper typographic quotes in English, too:

ERROR: column “t.somecolumn” does not exist
Hint: Perhaps you meant to reference the column “t.someColumn”.

But that's optically not very different (depends on your font, of
course) and your terminal has to support Unicode (or at least a subset
which includes those quotes).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature