Syntax error for UPDATE ... RETURNING INTO STRICT

[Alexander Farber]

Good morning,

why does not PostgreSQL 10.11 please like the -

CREATE OR REPLACE FUNCTION words_toggle_puzzle(
in_mid bigint
) RETURNS table (
out_puzzle boolean
) AS
$func$
UPDATE words_moves
SET puzzle = NOT puzzle
WHERE mid = in_mid
RETURNING puzzle
INTO STRICT out_puzzle;
$func$ LANGUAGE sql;

and fails with -

ERROR:  42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
 ^
LOCATION:  scanner_yyerror, scan.l:1128

Thank you
Alex

P.S: Here the table description, with mid being the PK:

words_ru=> \d words_moves
  Table "public.words_moves"
 Column  |   Type   | Collation | Nullable
| Default
-+--+---+--+--
 mid | bigint   |   | not null |
nextval('words_moves_mid_seq'::regclass)
 action  | text |   | not null |
 gid | integer  |   | not null |
 uid | integer  |   | not null |
 played  | timestamp with time zone |   | not null |
 tiles   | jsonb|   |  |
 score   | integer  |   |  |
 letters | text |   |  |
 hand| text |   |  |
 puzzle  | boolean  |   | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

RE: Syntax error for UPDATE ... RETURNING INTO STRICT

[Patrick FICHE]

Hi Alexander,

It seems that STRICT is the issue.
But why does your function return a table of boolean in this case ?
As it only updates one record, it would probably be easier to return a boolean 
only.
CREATE OR REPLACE FUNCTION words_toggle_puzzle(
in_mid bigint
) RETURNS boolean
 AS
$func$
UPDATE words_moves
SET puzzle = NOT puzzle
WHERE mid = in_mid
RETURNING puzzle;
$func$ LANGUAGE sql;
Regards,

Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96

[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]

From: Alexander Farber 
Sent: Tuesday, December 3, 2019 11:12 AM
To: pgsql-general 
Subject: Syntax error for UPDATE ... RETURNING INTO STRICT

Good morning,

why does not PostgreSQL 10.11 please like the -

CREATE OR REPLACE FUNCTION words_toggle_puzzle(
in_mid bigint
) RETURNS table (
out_puzzle boolean
) AS
$func$
UPDATE words_moves
SET puzzle = NOT puzzle
WHERE mid = in_mid
RETURNING puzzle
INTO STRICT out_puzzle;
$func$ LANGUAGE sql;
and fails with -

ERROR:  42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
 ^
LOCATION:  scanner_yyerror, scan.l:1128
Thank you
Alex

P.S: Here the table description, with mid being the PK:

words_ru=> \d words_moves
  Table "public.words_moves"
 Column  |   Type   | Collation | Nullable | 
Default
-+--+---+--+--
 mid | bigint   |   | not null | 
nextval('words_moves_mid_seq'::regclass)
 action  | text |   | not null |
 gid | integer  |   | not null |
 uid | integer  |   | not null |
 played  | timestamp with time zone |   | not null |
 tiles   | jsonb|   |  |
 score   | integer  |   |  |
 letters | text |   |  |
 hand| text |   |  |
 puzzle  | boolean  |   | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON 
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON 
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) 
REFERENCES words_moves(mid) ON DELETE CASCADE

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

[Alexander Farber]

Thank you Patrick -

On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE 
wrote:

>
> It seems that STRICT is the issue.
>
> But why does your function return a table of boolean in this case ?
>
> As it only updates one record, it would probably be easier to return a
> boolean only.
>
> CREATE OR REPLACE FUNCTION words_toggle_puzzle(
> in_mid bigint
> ) RETURNS boolean
>  AS
> $func$
> UPDATE words_moves
> SET puzzle = NOT puzzle
> WHERE mid = in_mid
> RETURNING puzzle;
> $func$ LANGUAGE sql;
>

your suggestion works well, thank you.

I wanted to use strict, because the mid is a PK - so there should always be
an exactly one record that has been updated

(or otherwise, in very strange cases - the SQL would fail and my
java-servlet would throw SQLException)

Regards
Alex

Re: slow insert speeds with bytea

[Jeff Janes]

On Mon, Dec 2, 2019 at 4:42 AM Alex O'Ree  wrote:

> Is there anything I can to increase insert speeds for bytea? Currently
> running postgres 9.6.15
>
> I have a few tables without a bytea and a few with bytea. There is a large
> performance difference with inserts between the two. I'm inserting a byte[]
> that's usually less than 1MB on content. The content itself is actually
> just utf8 string data.
>
> For the non-bytea table, inserts can be as high as 40k rows/sec, whereas
> the bytea table is closer to 4k/sec or less.
>
> If this is just a limitation of postgres, then that's fine but the
> performance delta is so significant that i feel like i'm missing something
>

That does seem pretty drastic.  But I don't think we will get
anywhere unless you post your actual benchmarking program so that we can
try it for ourselves.  I certainly don't see anything that drastic in my
own testing.  Maybe the bottleneck is on the client side.

Cheers,

Jeff

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

[Tom Lane]

Alexander Farber  writes:
> why does not PostgreSQL 10.11 please like the -

I think you are confusing plpgsql syntax with sql syntax.

regards, tom lane

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

[Adrian Klaver]

On 12/3/19 3:37 AM, Alexander Farber wrote:

Thank you Patrick -

On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE > wrote:



It seems that STRICT is the issue.

But why does your function return a table of boolean in this case ?

As it only updates one record, it would probably be easier to return
a boolean only.

CREATE OR REPLACE FUNCTION words_toggle_puzzle(
     in_mid bigint
     ) RETURNS boolean
  AS
$func$
     UPDATE words_moves
     SET puzzle = NOT puzzle
     WHERE mid = in_mid
     RETURNING puzzle;
$func$ LANGUAGE sql;


your suggestion works well, thank you.

I wanted to use strict, because the mid is a PK - so there should always 
be an exactly one record that has been updated


Which you will get without STRICT:

https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

"For INSERT/UPDATE/DELETE with RETURNING, PL/pgSQL reports an error for 
more than one returned row, even when STRICT is not specified. This is 
because there is no option such as ORDER BY with which to determine 
which affected row should be returned."



Though I still not sure what was wrong with your initial attempt?:


ERROR:  42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
^
LOCATION:  scanner_yyerror, scan.l:1128

From the error it looks like a hidden space issue or something.



(or otherwise, in very strange cases - the SQL would fail and my 
java-servlet would throw SQLException)


Regards
Alex




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

Issue upgrading from V11 to V12 on Debian

[stan]

I am working on upgrading from V11 to V12 on Debian.

My first attempt failed, and I have figured out that this is because I have
added extensions to the V11 DB, at least one of which was not installed
using  the Debian packages.

So, it looks like i need to install these before doing the upgrade, correct?

This brings up a chicken or egg issue however. We are creating the
extensions, like this:

CREATE EXTENSION emailaddr with schema ica 

So, to accomplish this, I would need to create the ice schema, and the user
it "belongs" to before I run pg_upgradecluster. This raises 2 questions

1) Is it OK to create these before doing the upgrade?
2) Does this flag that creating these extensions "in" a schema may be a
long term "bad idea"?

Thanks for any advice n this.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Issue upgrading from V11 to V12 on Debian

[Tom Lane]

stan  writes:
> I am working on upgrading from V11 to V12 on Debian.
> My first attempt failed, and I have figured out that this is because I have
> added extensions to the V11 DB, at least one of which was not installed
> using  the Debian packages.

> So, it looks like i need to install these before doing the upgrade, correct?

Yes, but in this context, "install" only means "add the files to the
filesystem".  You don't need to do CREATE EXTENSION in the new
database; pg_upgrade will handle that when it transfers the data.

regards, tom lane

Re: Issue upgrading from V11 to V12 on Debian

[Paul Jungwirth]

On 12/3/19 7:53 AM, stan wrote:

I am working on upgrading from V11 to V12 on Debian.

My first attempt failed, and I have figured out that this is because I have
added extensions to the V11 DB, at least one of which was not installed
using  the Debian packages.

So, it looks like i need to install these before doing the upgrade, correct?

This brings up a chicken or egg issue however. We are creating the
extensions, like this:

CREATE EXTENSION emailaddr with schema ica

So, to accomplish this, I would need to create the ice schema, and the user
it "belongs" to before I run pg_upgradecluster. This raises 2 questions


There are two parts to adding extensions. First you install the *files* 
the extension needs. If you build from source this happens when you say 
`make install`. Or if you use pgxn, when you say `pgxn install foo`. On 
Ubuntu this will add the *.sql and *.control files to 
/usr/share/postgresql/12/extension, and the *.so files to 
/usr/lib/postgresql/12/lib (so I assume Debian is similar). You can do 
that before running pg_upgradecluster.


The second part is saying CREATE EXTENSION, which adds the extension to 
the current database. pg_upgradecluster should handle that for you; it 
shouldn't require any extra steps.


I hope this helps!

Yours,

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

[Alexander Farber]

Thanks for your replies!

Tom has hinted that STRICT is pl/pgSQL syntax and not SQL

Regards
Alex

>

Re: Issue upgrading from V11 to V12 on Debian

[stan]

On Tue, Dec 03, 2019 at 11:13:55AM -0500, Tom Lane wrote:
> stan  writes:
> > I am working on upgrading from V11 to V12 on Debian.
> > My first attempt failed, and I have figured out that this is because I have
> > added extensions to the V11 DB, at least one of which was not installed
> > using  the Debian packages.
> 
> > So, it looks like i need to install these before doing the upgrade, correct?
> 
> Yes, but in this context, "install" only means "add the files to the
> filesystem".  You don't need to do CREATE EXTENSION in the new
> database; pg_upgrade will handle that when it transfers the data.

Sorry, I am back again :-(

So, I have V12 running as the default on the machine I am testing this on
now:

Ver Cluster Port Status OwnerData directory  Log file
11  main5433 down   postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

BUT, I went to the directory where I have the exentsion's source, did a
make clean ; make ; make install, and the files were still installed in the
V11 tree. How can I instruct the system to put these in the V12 tree?

The  extension in question is pgemailaddr if this matters, BTW.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Issue upgrading from V11 to V12 on Debian

[Paul Jungwirth]

On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default 
on the machine I am testing this on

now:

Ver Cluster Port Status OwnerData directory  Log file
11  main5433 down   postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

BUT, I went to the directory where I have the exentsion's source, did a
make clean ; make ; make install, and the files were still installed in the
V11 tree. How can I instruct the system to put these in the V12 tree?

The  extension in question is pgemailaddr if this matters, BTW.


That extension (and every extension I've seen) uses Postgres's normal 
extension-building infrastructure, so it runs pg_config to learn where 
to put files. If you run pg_config on your system it will probably 
report directories belonging to v11. (That's a little surprising because 
on Ubuntu systems I've always had it report the latest version.)


Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to 
specific which cluster to use. If you want to add that to your pg_config 
you could do it like this: https://stackoverflow.com/a/43403193/122087 
Then just set PGCLUSTER before building. (Make sure you `make clean` first.)


--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: Issue upgrading from V11 to V12 on Debian

[stan]

On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:
> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> the machine I am testing this on
> > now:
> > 
> > Ver Cluster Port Status OwnerData directory  Log file
> > 11  main5433 down   postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main5432 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> > 
> > BUT, I went to the directory where I have the exentsion's source, did a
> > make clean ; make ; make install, and the files were still installed in the
> > V11 tree. How can I instruct the system to put these in the V12 tree?
> > 
> > The  extension in question is pgemailaddr if this matters, BTW.
> 
> That extension (and every extension I've seen) uses Postgres's normal
> extension-building infrastructure, so it runs pg_config to learn where to
> put files. If you run pg_config on your system it will probably report
> directories belonging to v11. (That's a little surprising because on Ubuntu
> systems I've always had it report the latest version.)
> 
> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> which cluster to use. If you want to add that to your pg_config you could do
> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> PGCLUSTER before building. (Make sure you `make clean` first.)
> 

Indeed it does.

I to am surprised. What I find actually surprising is that it is the V12
server that is running.

Here is a bit of history.

I 1st installed V12 quite some time ago on this machine. At that point in
time the V12 client were active, but the V11 server was still active, and
the V12 one was, I believe, not running. So this morning I started trying to
do the upgrade. Using this as an reference:

//gist.gihttpsthub.com/dmitrykustov/27c673ec4f7abd716912e4c830910019

Since I did not know enogh, this failed. So I ran apt-get purge on the V12
server, got the V11 server working, and rebuilt my database, which was an
interesting learning experience, as I thought I had scripts to do this, but
of course the tests I had run on them were against a DB with various things
that somehow I had set up by hand, and not implemented in the scripts.
Learning experience for certain.

So, when I re-installed V12, it started either started up on the default
port, or the upgrade process that failed may have done something. I cannot
remember. So I probably should run the Debian alternative selection tool,
and set V1 as the default, but I really do not understand the consequences
of that vis a vi the upgrade tool.

Should I do this?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Issue upgrading from V11 to V12 on Debian

[stan]

On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:
> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> the machine I am testing this on
> > now:
> > 
> > Ver Cluster Port Status OwnerData directory  Log file
> > 11  main5433 down   postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main5432 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> > 
> > BUT, I went to the directory where I have the exentsion's source, did a
> > make clean ; make ; make install, and the files were still installed in the
> > V11 tree. How can I instruct the system to put these in the V12 tree?
> > 
> > The  extension in question is pgemailaddr if this matters, BTW.
> 
> That extension (and every extension I've seen) uses Postgres's normal
> extension-building infrastructure, so it runs pg_config to learn where to
> put files. If you run pg_config on your system it will probably report
> directories belonging to v11. (That's a little surprising because on Ubuntu
> systems I've always had it report the latest version.)
> 
> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> which cluster to use. If you want to add that to your pg_config you could do
> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> PGCLUSTER before building. (Make sure you `make clean` first.)
> 
Well, this gets more interesting. I decided to try the reinstall thing
agai, so I did an apt-get purge on the V12 server, started the V11 server
(admiitely I did not check to see what port it started on), reinstalled
the V1 package, and now BOTH are running, with eh V12 server on 5432 and
the V11 on 5433.



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Issue upgrading from V11 to V12 on Debian

[Paul Jungwirth]

On 12/3/19 9:41 AM, stan wrote:

Well, this gets more interesting. I decided to try the reinstall thing
agai, so I did an apt-get purge on the V12 server, started the V11 server
(admiitely I did not check to see what port it started on), reinstalled
the V1 package, and now BOTH are running, with eh V12 server on 5432 and
the V11 on 5433.


Yes, Ubuntu/Debian make it easy to run multiple versions/clusters at 
once. I don't think they even use the alternatives system (but I could 
be wrong). For building/installing the extension, the only important 
part AIUI is getting pg_config to point at the right files. My own 
approach has always been to hack the bash code to accept a PGCLUSTER 
envvar, as in that Stack Overflow answer. Someone here might have a 
better approach though.


Regards,

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: Issue upgrading from V11 to V12 on Debian

[stan]

On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:
> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> the machine I am testing this on
> > now:
> > 
> > Ver Cluster Port Status OwnerData directory  Log file
> > 11  main5433 down   postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main5432 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> > 
> > BUT, I went to the directory where I have the exentsion's source, did a
> > make clean ; make ; make install, and the files were still installed in the
> > V11 tree. How can I instruct the system to put these in the V12 tree?
> > 
> > The  extension in question is pgemailaddr if this matters, BTW.
> 
> That extension (and every extension I've seen) uses Postgres's normal
> extension-building infrastructure, so it runs pg_config to learn where to
> put files. If you run pg_config on your system it will probably report
> directories belonging to v11. (That's a little surprising because on Ubuntu
> systems I've always had it report the latest version.)
> 
> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> which cluster to use. If you want to add that to your pg_config you could do
> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> PGCLUSTER before building. (Make sure you `make clean` first.)
> 
And weirder. 

So I purged the V1 server again, edited the postgresql.conf file to put it
back to port 5432, reinstalled the V12 package, and now:

stan@stantest:~/src/pgemailaddr-master$ pg_lsclusters
Ver Cluster Port Status OwnerData directory  Log file
11  main5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5434 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

So, I decided to take a quick look at the production machine, just to make
sure this matched up to what was going on over there, and look what I found:

stan@ica-db:~$ htop
stan@ica-db:~$ pg_lsclusters
Ver Cluster Port Status OwnerData directory  Log file
11  main5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-12-main.log

This seems impossible to me.



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Issue upgrading from V11 to V12 on Debian

[Daniel Verite]

stan wrote:

> BUT, I went to the directory where I have the exentsion's source, did a
> make clean ; make ; make install, and the files were still installed in the
> V11 tree. How can I instruct the system to put these in the V12 tree?

With the Debian packaging, /usr/bin/pg_config is a shell script that
has this comment on top:

# If postgresql-server-dev-* is installed, call pg_config from the latest
# available one. Otherwise fall back to libpq-dev's version.

According to this comment, invoking /usr/bin/pg_config refers to the v11
server probably because you didn't install postgresql-server-dev-12 yet.

When you'll have both postgresql-server-dev-11 and postgresql-server-dev-12
installed, independently of which clusters are actually running,
you can still force a specific target version when compiling an extension
with :
 $ make PG_CONFIG=/usr/lib/postgresql/11/bin/pg_config [install]
or
 $ make PG_CONFIG=/usr/lib/postgresql/12/bin/pg_config [install]


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

[Adrian Klaver]

On 12/3/19 8:24 AM, Alexander Farber wrote:

Thanks for your replies!

Tom has hinted that STRICT is pl/pgSQL syntax and not SQL


I finally read the full function and see you declared the LANGUAGE as 
sql. Now things make sense:)




Regards
Alex




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

Re: Issue upgrading from V11 to V12 on Debian

[Adrian Klaver]

On 12/3/19 9:51 AM, stan wrote:


On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:

On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
the machine I am testing this on

now:

Ver Cluster Port Status OwnerData directory  Log file
11  main5433 down   postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

BUT, I went to the directory where I have the exentsion's source, did a
make clean ; make ; make install, and the files were still installed in the
V11 tree. How can I instruct the system to put these in the V12 tree?

The  extension in question is pgemailaddr if this matters, BTW.


That extension (and every extension I've seen) uses Postgres's normal
extension-building infrastructure, so it runs pg_config to learn where to
put files. If you run pg_config on your system it will probably report
directories belonging to v11. (That's a little surprising because on Ubuntu
systems I've always had it report the latest version.)

Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
which cluster to use. If you want to add that to your pg_config you could do
it like this: https://stackoverflow.com/a/43403193/122087 Then just set
PGCLUSTER before building. (Make sure you `make clean` first.)


And weirder.

So I purged the V1 server again, edited the postgresql.conf file to put it
back to port 5432, reinstalled the V12 package, and now:

stan@stantest:~/src/pgemailaddr-master$ pg_lsclusters
Ver Cluster Port Status OwnerData directory  Log file
11  main5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5434 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

So, I decided to take a quick look at the production machine, just to make
sure this matched up to what was going on over there, and look what I found:

stan@ica-db:~$ htop
stan@ica-db:~$ pg_lsclusters
Ver Cluster Port Status OwnerData directory  Log file
11  main5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-12-main.log

This seems impossible to me.



What does ps ax | grep post show?







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

Re: Issue upgrading from V11 to V12 on Debian

[stan]

On Tue, Dec 03, 2019 at 11:35:40AM -0800, Adrian Klaver wrote:
> On 12/3/19 9:51 AM, stan wrote:
> > 
> > On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:
> > > On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> > > the machine I am testing this on
> > > > now:
> > > > 
> > > > Ver Cluster Port Status OwnerData directory  Log file
> > > > 11  main5433 down   postgres /var/lib/postgresql/11/main
> > > > /var/log/postgresql/postgresql-11-main.log
> > > > 12  main5432 online postgres /var/lib/postgresql/12/main
> > > > /var/log/postgresql/postgresql-12-main.log
> > > > 
> > > > BUT, I went to the directory where I have the exentsion's source, did a
> > > > make clean ; make ; make install, and the files were still installed in 
> > > > the
> > > > V11 tree. How can I instruct the system to put these in the V12 tree?
> > > > 
> > > > The  extension in question is pgemailaddr if this matters, BTW.
> > > 
> > > That extension (and every extension I've seen) uses Postgres's normal
> > > extension-building infrastructure, so it runs pg_config to learn where to
> > > put files. If you run pg_config on your system it will probably report
> > > directories belonging to v11. (That's a little surprising because on 
> > > Ubuntu
> > > systems I've always had it report the latest version.)
> > > 
> > > Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> > > which cluster to use. If you want to add that to your pg_config you could 
> > > do
> > > it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> > > PGCLUSTER before building. (Make sure you `make clean` first.)
> > > 
> > And weirder.
> > 
> > So I purged the V1 server again, edited the postgresql.conf file to put it
> > back to port 5432, reinstalled the V12 package, and now:
> > 
> > stan@stantest:~/src/pgemailaddr-master$ pg_lsclusters
> > Ver Cluster Port Status OwnerData directory  Log file
> > 11  main5432 online postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main5434 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> > 
> > So, I decided to take a quick look at the production machine, just to make
> > sure this matched up to what was going on over there, and look what I found:
> > 
> > stan@ica-db:~$ htop
> > stan@ica-db:~$ pg_lsclusters
> > Ver Cluster Port Status OwnerData directory  Log file
> > 11  main5432 online postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main5432 online postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-12-main.log
> > 
> > This seems impossible to me.
> > 
> 
> What does ps ax | grep post show?
> 

On the production machine, just teh V11 biaries.

Looks like the pg_lsclusters methond may not be totaly relaible.

BGot the test machine upgraded.

Thanks to all the helpful folks that provided expert advice on this.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

upgrade and migrate

[Julie Nishimura]

Hello, what is the best way to migrate from PostgreSQL 8.3.11 on 
x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu server, 
with minimal downtime?
The caveat is the source has about 80 databases overall almost 30 TB. I could 
migrate the smallest ones (up to 1 tb) using pg_dump and pg_restore, but the 
largest hot database is almost 17 tb, and I am not sure how to approach this 
effort in a better and efficient way?

Thank you!
-Julie N

Re: pg_basebackup + incremental base backups

[Stephen Frost]

Greetings,

* Christopher Pereira (krip...@imatronix.cl) wrote:
> Our stream replication slave server got out of sync so we need to base
> backup again.

If you do WAL archiving instead of depending on the WAL to exist on the
primary then a replica can catch up using WAL.  Having a WAL archive
also means you can do point-in-time-recovery for any point.

> In case of big databases, can we do incremental backups with pg_basebackup?

pg_basebackup doesn't support incremental backups, though there's been
discussion of adding some kind of support for it, check -hackers if
you're curious.

> Is there any alternative?

There's a few different alternatives for PG backup software, some of
which support incremental backups and restores.  I'm personally involved
with pgbackrest- https://pgbackrest.org though there's other options out
there also (wal-g, barman, and more).

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: upgrade and migrate

[Michael Paquier]

On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote:
> Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
> x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
> server, with minimal downtime?
> The caveat is the source has about 80 databases overall almost 30
> TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
> pg_restore, but the largest hot database is almost 17 tb, and I am
> not sure how to approach this effort in a better and efficient way?

pg_upgrade could be one way to go here.  That's not the scale pg_dump
would be very good at.  I would have personally avoided using pg_dump
above 10~20GB.  Depending on the downtime you are ready to accept,
a migration based on Slony could be something to investigate.
--
Michael


signature.asc
Description: PGP signature

Re: upgrade and migrate

[Ron]

On 12/3/19 10:48 PM, Michael Paquier wrote:

On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote:

Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
server, with minimal downtime?
The caveat is the source has about 80 databases overall almost 30
TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
pg_restore, but the largest hot database is almost 17 tb, and I am
not sure how to approach this effort in a better and efficient way?

pg_upgrade could be one way to go here.  That's not the scale pg_dump
would be very good at.  I would have personally avoided using pg_dump
above 10~20GB.


I've successfully migrated three 1-2 TB databases across data centers in 
about 12 hours each, from 8.4 to 9.6.  Used eight threads, and the 9.6 
pg_dump against the 8.4 database.  17TB is a "bridge too far", though...



Depending on the downtime you are ready to accept,
a migration based on Slony could be something to investigate.
--
Michael


--
Angular momentum makes the world go 'round.

Re: upgrade and migrate

[Thomas Kellerer]

Michael Paquier schrieb am 04.12.2019 um 05:48:
> On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote:
>> Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
>> x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
>> server, with minimal downtime?
>> The caveat is the source has about 80 databases overall almost 30
>> TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
>> pg_restore, but the largest hot database is almost 17 tb, and I am
>> not sure how to approach this effort in a better and efficient way?
> 
> pg_upgrade could be one way to go here.  That's not the scale pg_dump
> would be very good at.  

But pg_upgrade only supports 8.4+ 

At least according to the manual.

Re: upgrade and migrate

[Laurenz Albe]

On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote:
> On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote:
> > Hello, what is the best way to migrate from PostgreSQL 8.3.11 on
> > x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu
> > server, with minimal downtime?
> > The caveat is the source has about 80 databases overall almost 30
> > TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and
> > pg_restore, but the largest hot database is almost 17 tb, and I am
> > not sure how to approach this effort in a better and efficient way?
> 
> pg_upgrade could be one way to go here.  That's not the scale pg_dump
> would be very good at.  I would have personally avoided using pg_dump
> above 10~20GB.  Depending on the downtime you are ready to accept,
> a migration based on Slony could be something to investigate.

Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.

I would upgrade to a version more recent than 9.6.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com