Re:
On Mon, May 23, 2022, 11:33 AM Rama Krishnan wrote: > Hi, > > After pg_repack do we face any perfomancr issue or any other issues in > production DB >
list tablespaces named in custom format dump
Using pg 14, is it easy (possible) to list the tablespaces used in a custom format dump. Are the definitions included? Thanks,
Re: list tablespaces named in custom format dump
On 5/23/22 9:45 AM, Rob Sargent wrote: Using pg 14, is it easy (possible) to list the tablespaces used in a custom format dump. Are the definitions included? https://www.postgresql.org/docs/14/app-pgdump.html "pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall." https://www.postgresql.org/docs/14/app-pg-dumpall.html "-g --globals-only Dump only global objects (roles and tablespaces), no databases. " Thanks, -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
On 5/23/22 10:50, Adrian Klaver wrote: On 5/23/22 9:45 AM, Rob Sargent wrote: Using pg 14, is it easy (possible) to list the tablespaces used in a custom format dump. Are the definitions included? https://www.postgresql.org/docs/14/app-pgdump.html "pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall." https://www.postgresql.org/docs/14/app-pg-dumpall.html "-g --globals-only Dump only global objects (roles and tablespaces), no databases. " Roger that, but does the dump of a database name the tablespaces from which the objects were retrieved? And if so, is that accessible?
Re: list tablespaces named in custom format dump
On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: Roger that, but does the dump of a database name the tablespaces from which the objects were retrieved? And if so, is that accessible? https://www.postgresql.org/docs/14/app-pgrestore.html "--no-tablespaces Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore. " Would indicate it does. I don't have cluster set up with tablespaces to test, but I would try: pg_restore -l This will produce a Table of Contents for the file. -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: Roger that, but does the dump of a database name the tablespaces from which the objects were retrieved? And if so, is that accessible? https://www.postgresql.org/docs/14/app-pgrestore.html "--no-tablespaces Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore. " Would indicate it does. I don't have cluster set up with tablespaces to test, but I would try: pg_restore -l This will produce a Table of Contents for the file. Ah, missed that. Thanks. (Now I'll have to find the version used to dump the db...)
Re: list tablespaces named in custom format dump
On 5/23/22 10:01 AM, Rob Sargent wrote: On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: Roger that, but does the dump of a database name the tablespaces from which the objects were retrieved? And if so, is that accessible? https://www.postgresql.org/docs/14/app-pgrestore.html "--no-tablespaces Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore. " Would indicate it does. I don't have cluster set up with tablespaces to test, but I would try: pg_restore -l This will produce a Table of Contents for the file. Ah, missed that. Thanks. (Now I'll have to find the version used to dump the db...) If you have it just use the Postgres 14 version of pg_restore it will be able to read older files from older versions of pg_dump. -- Adrian Klaver adrian.kla...@aklaver.com
Re: PLPGSQL - extra column existence in trigger
Hello! Peter J. Holzer ezt írta (időpont: 2022. máj. 11., Sze, 0:44): > On 2022-05-07 15:02:09 +0200, Durumdara wrote: > > > So in the new trigger we can't load them all with: > > How do you get a new trigger on one table but not the new columns on the > other table? Wouldn't you update both at the same time? > The needed columns are in a temporary table. Each Win32 application creates his own temp table with User Informations, like ID, Name, Computer Info. These are for logging purposes. The trigger is in another table, and I want to log the actual user information with the row changing to see who caused it. Best regards, dd
Re: list tablespaces named in custom format dump
On 5/23/22 11:07, Adrian Klaver wrote: On 5/23/22 10:01 AM, Rob Sargent wrote: On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: Roger that, but does the dump of a database name the tablespaces from which the objects were retrieved? And if so, is that accessible? https://www.postgresql.org/docs/14/app-pgrestore.html "--no-tablespaces Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore. " Would indicate it does. I don't have cluster set up with tablespaces to test, but I would try: pg_restore -l This will produce a Table of Contents for the file. Ah, missed that. Thanks. (Now I'll have to find the version used to dump the db...) If you have it just use the Postgres 14 version of pg_restore it will be able to read older files from older versions of pg_dump. This is great news. I do have pg14. I thought you had to dump with the save version as restore.
psql connect over ssl load balancer
Hi. I want to secure connections to postgress server usings SSL. My server is deployed on google cloud behind SSL load balancer, which terminates SSL connections (using SSL certificates, issued by google) and forwards decrypted traffic to the posgres server. I can connect without problems from nodejs using TLS socket. But standard psql client don't working: $ PGSSLMODE=require psql -h proxy.softvisio.net psql: error: connection to server at "proxy.softvisio.net" (34.149.158.159), port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Seems, that psql will not work withour private keys? But I am not sure. Could somebody help me to understand, how I can use it?
Re: list tablespaces named in custom format dump
Adrian Klaver writes: > On 5/23/22 9:54 AM, Rob Sargent wrote: >> Roger that, but does the dump of a database name the tablespaces from >> which the objects were retrieved? And if so, is that accessible? > Would indicate it does. I don't have cluster set up with tablespaces to > test, but I would try: > pg_restore -l AFAICS, the output produced by -l doesn't mention objects' tablespaces. You could see them by extracting the plain text archive and grepping for object comments that include a tablespace name: pg_restore -s -f - archive_file | grep Tablespace: It looks to me like that will only appear for tables with non-default tablespaces. regards, tom lane
Re: psql connect over ssl load balancer
"dzagas...@gmail.com" writes: > I want to secure connections to postgress server usings SSL. > My server is deployed on google cloud behind SSL load balancer, which > terminates SSL connections (using SSL certificates, issued by google) > and forwards decrypted traffic to the posgres server. > But standard psql client don't working: > $ PGSSLMODE=require psql -h proxy.softvisio.net That isn't going to work, because PG wraps the SSL handshake inside its own protocol. You could set up an SSL tunnel and let psql think it's making a non-encrypted connection. regards, tom lane
Can I start Update row in After Insert trigger function?
Hello! I need to log some row changes in a table with JSON format (row_to_json). In the Before Update I can select the row into a column with JSON format. And in After Update I can log this column into an archive table. Plus I have the prior data in the JSON_TEXT field. It's ok. FUNCTION Before_Update ... BEGIN select row_to_json(thistable) into json_text_var from thistable where id = OLD.id; NEW.json_text = json_text_var; RETURN NEW; END; FUNCTION After_Update ... BEGIN insert into logtable select 'thistable', NEW.id, NEW.json_TEXT; RETURN NEW; END; But this technique isn't working in insert, because no OLD.id. and OLD row: select row_to_json(thistable) into json_text_var from thistable where id = ???.id; No row!!! Only NEW.nn variables. Only way if I call an update: FUNCTION After_Insert ... BEGIN -- We try to update the log with a dummy update -- This calls Before/After Update, logs + fills the json field update thistable set json_text = json_text where id = NEW.id; RETURN NEW; END; Does this cause problems? If I call an Update in the Row before After_Insert finishes? Version: PGSQL 9.6-11 Thank you for your help! Best regards, dd
Re: psql connect over ssl load balancer
Ah, thanks. This will make thing more complex. On 23.05.2022 20:32, Tom Lane wrote: "dzagas...@gmail.com" writes: I want to secure connections to postgress server usings SSL. My server is deployed on google cloud behind SSL load balancer, which terminates SSL connections (using SSL certificates, issued by google) and forwards decrypted traffic to the posgres server. But standard psql client don't working: $ PGSSLMODE=require psql -h proxy.softvisio.net That isn't going to work, because PG wraps the SSL handshake inside its own protocol. You could set up an SSL tunnel and let psql think it's making a non-encrypted connection. regards, tom lane
Re: Can I start Update row in After Insert trigger function?
On 5/23/22 10:35 AM, Durumdara wrote: Hello! I need to log some row changes in a table with JSON format (row_to_json). In the Before Update I can select the row into a column with JSON format. And in After Update I can log this column into an archive table. Plus I have the prior data in the JSON_TEXT field. You need to read: https://www.postgresql.org/docs/current/plpgsql-trigger.html But this technique isn't working in insert, because no OLD.id. and OLD row: select row_to_json(thistable) into json_text_var from thistable where id = ???.id; No row!!! Only NEW.nn variables. Yes an INSERT is a new value only there is no old row(value) available. In the doc link I posted: " TG_OP Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. " Use this to modify what value(NEW/OLD) you are working with. Call trigger with UPDATE and INSERT. Version: PGSQL 9.6-11 Thank you for your help! Best regards, dd -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
On 5/23/22 11:29, Tom Lane wrote: AFAICS, the output produced by -l doesn't mention objects' tablespaces. You could see them by extracting the plain text archive and grepping for object comments that include a tablespace name: pg_restore -s -f - archive_file | grep Tablespace: It looks to me like that will only appear for tables with non-default tablespaces. regards, tom lane That worked, thank you. I see it has only the "name" so I presume location can be redefined in the receiving cluster.
Re: list tablespaces named in custom format dump
On 5/23/22 10:19 AM, Rob Sargent wrote: On 5/23/22 11:07, Adrian Klaver wrote: On 5/23/22 10:01 AM, Rob Sargent wrote: On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: This is great news. I do have pg14. I thought you had to dump with the save version as restore pg_dump and pg_restore are backwards compatible. The recommendation is to use the same version of pg_dump as the server you are dumping if the plan is use it as backup to restore to same version of server. If you are planning on restoring to newer version then use the newer version of pg_dump to dump the older server version e.g. version 14 pg_dump dumps version 12 Postgres server. Then use the newer version of pg_restore(14 in this case) to restore to the Postgres 14 server. For the purposes of this question using the 14 version of pg_restore is acceptable to get the information. Actually doing a restore to a 14 version of the server with 12 version of a dump file may create problems as the 12 version of pg_dump may not include information needed by the newer server. -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
On 5/23/22 11:51, Adrian Klaver wrote: On 5/23/22 10:19 AM, Rob Sargent wrote: On 5/23/22 11:07, Adrian Klaver wrote: On 5/23/22 10:01 AM, Rob Sargent wrote: On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: This is great news. I do have pg14. I thought you had to dump with the save version as restore pg_dump and pg_restore are backwards compatible. The recommendation is to use the same version of pg_dump as the server you are dumping if the plan is use it as backup to restore to same version of server. If you are planning on restoring to newer version then use the newer version of pg_dump to dump the older server version e.g. version 14 pg_dump dumps version 12 Postgres server. Then use the newer version of pg_restore(14 in this case) to restore to the Postgres 14 server. For the purposes of this question using the 14 version of pg_restore is acceptable to get the information. Actually doing a restore to a 14 version of the server with 12 version of a dump file may create problems as the 12 version of pg_dump may not include information needed by the newer server. Thanks Adrian. There's enough wiggle room in there for me to at least try the restore. Linus file command tells me db.dump: PostgreSQL custom database dump - v1.13-0 That dump was from a version 10 server. Any guess at how that "v1.13-0" relates to the server version? Dumps from the current pg14 server say "v1.14-0" so it looks to me like the dumps were done with v13 pg-restore. This surprises me, but I do not manage the server. Maybe my dump file doesn't have anything pg14 restore can't handle.. we'll see Thanks again, all.
Re: list tablespaces named in custom format dump
On 5/23/22 11:04 AM, Rob Sargent wrote: Thanks Adrian. There's enough wiggle room in there for me to at least try the restore. Linus file command tells me db.dump: PostgreSQL custom database dump - v1.13-0 The pg_restore -l command will show the relevant information at top of file so: pg_restore -l pg14_test.out > toc.txt head -n 15 toc.txt ; ; Archive created at 2022-05-23 10:24:23 PDT ; dbname: test ; TOC Entries: 745 ; Compression: -1 ; Dump Version: 1.14-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 14.2 ; Dumped by pg_dump version: 14.2 ; ; ; Selected TOC Entries: That dump was from a version 10 server. Any guess at how that "v1.13-0" relates to the server version? Dumps from the current pg14 server say "v1.14-0" so it looks to me like the dumps were done with v13 pg-restore. This surprises me, but I do not manage the server. Maybe my dump file doesn't have anything pg14 restore can't handle.. we'll see I have abused this on occasion and succeeded, though sometimes it involves some tweaks. Thanks again, all. -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
Rob Sargent writes: > Thanks Adrian. There's enough wiggle room in there for me to at least > try the restore. Linus file command tells me > db.dump: PostgreSQL custom database dump - v1.13-0 > That dump was from a version 10 server. Any guess at how that "v1.13-0" > relates to the server version? It doesn't, it's just a more-or-less-magic identifier for the dump file's format version, with no connection to any user-visible release number. What you should try is pg_restore -f - db.dump | head which should provide some comments telling you the originating server version and pg_dump version. regards, tom lane
Re: list tablespaces named in custom format dump
On 5/23/22 12:15, Tom Lane wrote: Rob Sargent writes: Thanks Adrian. There's enough wiggle room in there for me to at least try the restore. Linus file command tells me db.dump: PostgreSQL custom database dump - v1.13-0 That dump was from a version 10 server. Any guess at how that "v1.13-0" relates to the server version? It doesn't, it's just a more-or-less-magic identifier for the dump file's format version, with no connection to any user-visible release number. What you should try is pg_restore -f - db.dump | head which should provide some comments telling you the originating server version and pg_dump version. regards, tom lane OK, back to reality: version 10 dump. The output of from head command is very different that Adrian shows but I'm still left giving pg_restore(14) a shot. Getting older server will be challenging. Much appreciated, rjs
Re: list tablespaces named in custom format dump
On 5/23/22 11:31, Rob Sargent wrote: On 5/23/22 12:15, Tom Lane wrote: pg_restore -f - db.dump | head which should provide some comments telling you the originating server version and pg_dump version. regards, tom lane OK, back to reality: version 10 dump. The output of from head command is very different that Adrian shows but I'm still left giving pg_restore(14) a shot. Getting older server will be challenging. Different view of the information. pg_restore -l has more detail, but all you are interested in is this: -- Dumped from database version 14.2 -- Dumped by pg_dump version 14.2 anyway. As to older version, how are you installing Postgres? Much appreciated, rjs -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
> On May 23, 2022, at 12:59 PM, Adrian Klaver wrote: > > On 5/23/22 11:31, Rob Sargent wrote: >> On 5/23/22 12:15, Tom Lane wrote: > >>> >>> pg_restore -f - db.dump | head >>> >>> which should provide some comments telling you the originating >>> server version and pg_dump version. >>> >>> regards, tom lane >> OK, back to reality: version 10 dump. >> The output of from head command is very different that Adrian shows but I'm >> still left giving pg_restore(14) a shot. Getting older server will be >> challenging. > > Different view of the information. > > pg_restore -l has more detail, but all you are interested in is this: > > -- Dumped from database version 14.2 > -- Dumped by pg_dump version 14.2 > > anyway. > > As to older version, how are you installing Postgres? > > >> Much appreciated, >> rjs Yeah, that’s part of the problem. I’m not installing postgres, another group has that control. I can get the answer for version 14 but maybe not for version 10. Currently these are “Rocky Linux” but the pg14 install could have been done on CentOS8. CentOS for version 10. I’ll see if I can nail that down.
Re: list tablespaces named in custom format dump
On 5/23/22 12:26, Rob Sargent wrote: As to older version, how are you installing Postgres? Much appreciated, rjs Yeah, that’s part of the problem. /I’m/ not installing postgres, another group has that control. I can get the answer for version 14 but maybe not for version 10. Currently these are “Rocky Linux” but the pg14 install could have been done on CentOS8. CentOS for version 10. I’ll see if I can nail that down. So the options are: 1) Try the version 14 pg_restore to version 14 instance of version 10 dump file. 2) Wait to see if the admins can/will install a Postgres 10 instance to restore to from version 10 dump file then dump from using version 14 pg_dump to restore Postgres 14 instance. 3) Spin up a VM either on cloud service or locally and install Postgres 10 and do the version 10 restore/version 14 pg_dump there. -- Adrian Klaver adrian.kla...@aklaver.com
Re: list tablespaces named in custom format dump
On 5/23/22 13:47, Adrian Klaver wrote: On 5/23/22 12:26, Rob Sargent wrote: As to older version, how are you installing Postgres? Much appreciated, rjs Yeah, that’s part of the problem. /I’m/ not installing postgres, another group has that control. I can get the answer for version 14 but maybe not for version 10. Currently these are “Rocky Linux” but the pg14 install could have been done on CentOS8. CentOS for version 10. I’ll see if I can nail that down. So the options are: 1) Try the version 14 pg_restore to version 14 instance of version 10 dump file. 2) Wait to see if the admins can/will install a Postgres 10 instance to restore to from version 10 dump file then dump from using version 14 pg_dump to restore Postgres 14 instance. 3) Spin up a VM either on cloud service or locally and install Postgres 10 and do the version 10 restore/version 14 pg_dump there. Agreed. Unfortunately, in this case option 3 is illegal.
cast to domain with default collation issue.
CREATE DOMAIN testdomain AS text; --asume the default collation is as per show LC_COLLATE; – on my pc, it is C.UTF-8. --So the testdomain will be collation "C.UTF-8" => \d collate_test1 Table "test.collate_test1" Column | Type | Collation | Nullable | Default +-+---+--+- a | integer | | | b | text| en-x-icu | not null | => \d collate_test2 Table "test.collate_test2" Column | Type | Collation | Nullable | Default +-+---+--+- a | integer | | | b | text| sv-x-icu | | => \d collate_test3 Table "test.collate_test3" Column | Type | Collation | Nullable | Default +-+---+--+- a | integer | | | b | text| C | | --- My guess is that the following should be the same. Since the same content in the end will be cast to the same collation. However the following output contradicts with my understanding. SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; /* +---+-+ | a | b | +---+-+ | 1 | abc | | 4 | ABC | | 2 | äbc | | 3 | bbc | +---+-+ */ SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; /* +---+-+ | a | b | +---+-+ | 1 | abc | | 4 | ABC | | 3 | bbc | | 2 | äbc | +---+-+ */ SELECT a, b::testdomain FROM collate_test3 ORDER BY 2; /* +---+-+ | a | b | +---+-+ | 4 | ABC | | 1 | abc | | 3 | bbc | | 2 | äbc | +---+-+ */ -- I recommend David Deutsch's <> Jian
Re: cast to domain with default collation issue.
On Monday, May 23, 2022, jian he wrote: > CREATE DOMAIN testdomain AS text; > > --asume the default collation is as per show LC_COLLATE; > > – on my pc, it is C.UTF-8. > > --So the testdomain will be collation "C.UTF-8" > > > => \d collate_test1 > > Table "test.collate_test1" > > Column | Type | Collation | Nullable | Default > > +-+---+--+- > > a | integer | | | > > b | text| en-x-icu | not null | > > --- > > My guess is that the following should be the same. > > > My reading of the docs say this is consistent with outcome #2. https://www.postgresql.org/docs/current/collation.html David J.
Re: cast to domain with default collation issue.
"David G. Johnston" writes:
> On Monday, May 23, 2022, jian he wrote:
>> CREATE DOMAIN testdomain AS text;
>> --asume the default collation is as per show LC_COLLATE;
>> – on my pc, it is C.UTF-8.
>> --So the testdomain will be collation "C.UTF-8"
> My reading of the docs say this is consistent with outcome #2.
> https://www.postgresql.org/docs/current/collation.html
Yeah. The comments in parse_collate.c are clear that this behavior is
intentional:
case T_CoerceToDomain:
{
/*
* If the domain declaration included a non-default COLLATE
* spec, then use that collation as the output collation of
* the coercion. Otherwise allow the input collation to
* bubble up. (The input should be of the domain's base type,
* therefore we don't need to worry about it not being
* collatable when the domain is.)
*/
Perhaps this should be documented more clearly, but it's not obviously
wrong. If the domain declaration doesn't include an explicit COLLATE
then casting to the domain doesn't create an explicit collation
requirement. (That is, the domain *doesn't* have a specific
collation attached to it, any more than type text does.)
regards, tom lane
Re: Can I start Update row in After Insert trigger function?
Dear Adrian! Thank you for the information. For me the main question is that: Can I execute an UPDATE in the AFTER INSERT trigger procedure? Or is this confuses PGSQL, because prior operation (INSERT) isn't fully finished? Thank you! BR, dd Adrian Klaver ezt írta (időpont: 2022. máj. 23., H, 19:42): > On 5/23/22 10:35 AM, Durumdara wrote: > > Hello! > > > > I need to log some row changes in a table with JSON format (row_to_json). > > > > In the Before Update I can select the row into a column with JSON format. > > And in After Update I can log this column into an archive table. > > Plus I have the prior data in the JSON_TEXT field. > > You need to read: > https://www.postgresql.org/docs/current/plpgsql-trigger.html > > > > > But this technique isn't working in insert, because no OLD.id. and OLD > row: > > > > select row_to_json(thistable) into json_text_var > > from thistable where id = ???.id; > > No row!!! Only NEW.nn variables. > > Yes an INSERT is a new value only there is no old row(value) available. > In the doc link I posted: > > " > TG_OP > > Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE > telling for which operation the trigger was fired. > " > > Use this to modify what value(NEW/OLD) you are working with. Call > trigger with UPDATE and INSERT. > > > > > Version: PGSQL 9.6-11 > > > > Thank you for your help! > > > > Best regards, > > dd > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
