Logical replication
Good morning. I'm testing logical replication, but after creating subscription nothing happens, and I expected replication start. Details here: https://paste.debian.net/1152451/ Any help will be appreciated. Thanks. Javier
Re: Conflict with recovery on PG version 11.6
On Wed, 2020-06-17 at 08:42 +0300, Toomas Kristin wrote: > Basically after upgrade to version 11.5 from 10.6 I experience error messages > on streaming > replica host “FATAL: terminating connection due to conflict with recovery” > and > “ERROR: canceling statement due to conflict with recovery”. There is no > changes for > vacuuming on master nor max_standby_streaming_delay for replica. I tried to > correlate > errors with vacuuming process on master but according to logs there is no > link between > them. Somehow I have feeling that when query runs longer than value for > parameter > max_standby_streaming_delay the query will be terminated regardless vacuuming > process on master. > > Is there any changes on version 11.5 what may cause it? > > Is there any good solution without setting max_standby_streaming_delay=-1 or > enabling hot_standby_feedback? The basic behavior shouldn't have changed since v10. Check "pg_stat_database_conflicts" to see what kinds of conflicts that are. The only solutions to avoid queries being canceled due to replication conflicts are: 1. avoid that such conflicts happen: - set "hot_standby_feedback = on" on the standby and/or "vacuum_defer_cleanup_age" on the primary to avoid VACUUM conflicts - Don't lock tables in access exclusive mode 2. set "max_standby_streaming_delay" to -1 Note that it can be quite hard to completely avoid replication conflicts. Trying to have both no delay in applying changes and no cancelled queries is often not possible without seriously crippling autovacuum. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Importing a Large .ndjson file
Hi
I have a .ndjson file. It is a new-line-delimited JSON file. It is
about 10GB and has about 100,000 records.
Some sample records:
```
{ "key11": "value11", "key12": [ "value12.1", "value12.2"], "key13": {
"k111": "v111" } } \n\r
{ "key21": "value21", "key22": [ "value22.1", "value22.2"] }
```
Now I want to INSERT these json records into my postgres table of the
following schema:
```
CREATE TABLE myTable (id BIGSERIAL, content JSONB);
```
Where I want the records to be inserted to the `content` field of my
postgres table.
What is the best way to do this on a postgresql database, deployed in
kubernetes, with a 1 GB RAM allocated ?
I can probably write a that would read this file line-by-line and
INSERT into the database, in a transaction. But that I believe would
take a lot of network traffic and I want to know if there is a better
way to do this.
Thanks.
--
Sankar P
http://psankar.blogspot.com
Re: Conflict with recovery on PG version 11.6
Hi Laurenz, Thank you! What are reasons for conflicts? Based on documentation seems that the only reason can be that vacuum removed unused tuples that are in use at standby host and due to that standby host cannot apply modifications while blocking query either finishes or will be terminated. isnt it? Or there can be some other reasons? I just wondering what would be impact when I increase value for autovacuum_vacuum_scale_factor in order force vacuuming process postpone the clean up process. BR, Toomas > On 17. Jun 2020, at 12:42, Laurenz Albe wrote: > > On Wed, 2020-06-17 at 08:42 +0300, Toomas Kristin wrote: >> Basically after upgrade to version 11.5 from 10.6 I experience error >> messages on streaming >> replica host “FATAL: terminating connection due to conflict with recovery” >> and >> “ERROR: canceling statement due to conflict with recovery”. There is no >> changes for >> vacuuming on master nor max_standby_streaming_delay for replica. I tried to >> correlate >> errors with vacuuming process on master but according to logs there is no >> link between >> them. Somehow I have feeling that when query runs longer than value for >> parameter >> max_standby_streaming_delay the query will be terminated regardless >> vacuuming process on master. >> >> Is there any changes on version 11.5 what may cause it? >> >> Is there any good solution without setting max_standby_streaming_delay=-1 or >> enabling hot_standby_feedback? > > The basic behavior shouldn't have changed since v10. > > Check "pg_stat_database_conflicts" to see what kinds of conflicts that are. > > The only solutions to avoid queries being canceled due to replication > conflicts are: > > 1. avoid that such conflicts happen: > - set "hot_standby_feedback = on" on the standby and/or > "vacuum_defer_cleanup_age" on the primary to avoid VACUUM conflicts > - Don't lock tables in access exclusive mode > > 2. set "max_standby_streaming_delay" to -1 > > Note that it can be quite hard to completely avoid replication conflicts. > Trying to have both no delay in applying changes and no cancelled queries > is often not possible without seriously crippling autovacuum. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: Sv: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked
> In my experience vacuumlo, https://www.postgresql.org/docs/12/vacuumlo.html, is needed to remove large objects, before vacuum can remove them from pg_largeobject. Thanks for the suggestion! vacuumlo deletes any orphaned large objects, presumably by using lo_unlink. While we don't use vacuumlo, our service does clean up orphaned large objects in a similar way. And indeed, inspecting the database reveals that there are no orphaned large objects. Regards, Jim Hurne
Re: Importing a Large .ndjson file
Sankar P writes:
> I have a .ndjson file. It is a new-line-delimited JSON file. It is
> about 10GB and has about 100,000 records.
> Some sample records:
> { "key11": "value11", "key12": [ "value12.1", "value12.2"], "key13": {
> "k111": "v111" } } \n\r
> { "key21": "value21", "key22": [ "value22.1", "value22.2"] }
> What is the best way to do this on a postgresql database, deployed in
> kubernetes, with a 1 GB RAM allocated ?
It looks like plain old COPY would do this just fine, along the lines
of (in psql)
\copy myTable(content) from 'myfile.ndjson'
If the newlines actually are \n\r rather than the more usual \r\n,
you might have to clean that up to stop COPY from thinking they
represent two line endings not one.
I'd advise extracting the first hundred or so lines of the file and doing
a test import into a temporary table, just to verify the process.
regards, tom lane
Re: Minor Upgrade Question
Susan You can use -Uvh to upgrade the rpms on the existing machine. You can then use symlinks to link the expected pgsql data directories. Make sure you take a backup, and stop the service before you proceed. JD On Tue, Jun 16, 2020 at 7:12 AM Susan Joseph wrote: > So when I first started working with PostgreSQL I was using the latest > version (11.2). I don't want to move to 12 yet but I would like to get my > 11.2 up to 11.8. Due to my servers not being connected to the Internet I > ended up downloading the libraries and building the files locally. My > question is how do I upgrade to 11.8? I know how to go and get the rpms > now and download those to a disconnected server and then install PostgreSQL > that way. I was able to install 11.8 on another server using the rpms. > But my directories are different. The rpm install placed the files into > the directory /data/pgsql-11. My 11.2 database is in /data/pgsql. I > checked the rpm file and it says that the files are not relocatable. So I > can do a new install of 11.8 via the rpms and it will place the files into > /data/pgsql-11, can just need to copy the executable files in the > /data/pgsql-11/bin directory into my /data/pgsql/bin or are there other > files that need to be copied over? Is there a better way to do this rather > than reinstalling postgreSQL again on a server that already has it? > > Thanks, > Susan >
Re: Logical replication
Javi, What does your PostgreSQL log say about replication? JD On Wed, Jun 17, 2020 at 2:07 AM Javi Legido wrote: > Good morning. > > I'm testing logical replication, but after creating subscription nothing > happens, and I expected replication start. > > Details here: https://paste.debian.net/1152451/ > > Any help will be appreciated. > > Thanks. > > Javier >
Re: Logical replication
Many thanks for your reply. The last line of the subscriber says: ``` 2020-06-17 12:48:02.955 UTC [124] LOG: logical replication apply worker for subscription "my_subscription" has started ``` Let's keep this on hold for a while, since I guess that it's taking longer than expected, since it's a quite big database (more than 500 GB). Currently the pg_stat_replication.state is in 'catchup' for 2h now. I will reply to this thread as soon as I would have any news. Thanks again. On Wed, 17 Jun 2020 at 17:27, Joshua Drake wrote: > Javi, > > What does your PostgreSQL log say about replication? > > JD > > On Wed, Jun 17, 2020 at 2:07 AM Javi Legido wrote: > >> Good morning. >> >> I'm testing logical replication, but after creating subscription nothing >> happens, and I expected replication start. >> >> Details here: https://paste.debian.net/1152451/ >> >> Any help will be appreciated. >> >> Thanks. >> >> Javier >> >
ESQL/C no indicator variables ./. error -213
Hello, We encountered that if our ESQL/C written servers see on SELECT or FETCH in a row a NULL value, it will raise correctly the error -213 as written and explained in https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS We catch this error -213 and deal with in. What we did not knew and discovered today is something very fatal: In such a situation on a FETCH of a row of some 55 columns, the transfer of the column elements into their hostvariables stops on first NULL value, as here to be seen in the log: [29217] [17.06.2020 15:49:16:499]: ecpg_execute on line 69: query: select * from acq_ffleit where bnr = $1 ; with 1 parameter(s) on connection sisis [29217] [17.06.2020 15:49:16:500]: ecpg_execute on line 69: using PQexecParams [29217] [17.06.2020 15:49:16:500]: ecpg_free_params on line 69: parameter 1 = 742 [29217] [17.06.2020 15:49:16:500]: ecpg_process_output on line 69: correctly got 1 tuples with 55 fields [29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: 742 offset: 752; array: no ... [29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: 49 offset: 752; array: no [29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: offset: 752; array: no [29217] [17.06.2020 15:49:16:500]: raising sqlcode -213 on line 69: null value without indicator on line 69 In the above examples the transfer stopped after 47 RESULTs; the hostvariables after this have been untouched in our program, i.e. only part of the row has been read. One could say, "so what, you have error -213 and you deserve it". But at least this behaviour should be documented clearly in the above mentioned page. I would expect, that NULL values would not be transfered in such case but all other hostvariables yes, i.e. for me this is perhaps a bug in the ESQL/C implementation. Comments? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: ESQL/C no indicator variables ./. error -213
Matthias Apitz writes: > We encountered that if our ESQL/C written servers see on SELECT or FETCH > in a row a NULL value, it will raise correctly the error -213 as written > and explained in > https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS > We catch this error -213 and deal with in. > What we did not knew and discovered today is something very fatal: In > such a situation on a FETCH of a row of some 55 columns, the transfer of > the column elements into their hostvariables stops on first NULL value, > as here to be seen in the log: Could you provide a self-contained test case for this? It's hard to guess at what the problem might be. regards, tom lane
RE: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked
Michael Lewis wrote on 06/16/2020 04:41:16 PM:
> Still, if you run a manual vacuum analyze verbose, what sort of output
do you get?
On one of the instances that is exhibiting the "disk leak" behavior, the
VACUUM ANALYZE VERBOSE command doesn't generate any output or complete
before I loose the connection to the database (presumably because I hit a
connection read timeout). Is it possible to configure th read timeout for
psql?
One some of our healthy instances, we were able to run VACUUM ANALYZE
VERBOSE:
=> vacuum verbose analyze pg_largeobject;
INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: scanned index "pg_largeobject_loid_pn_index" to remove 630 row
versions
DETAIL: CPU: user: 0.06 s, system: 0.10 s, elapsed: 0.17 s
INFO: "pg_largeobject": removed 630 row versions in 190 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "pg_largeobject_loid_pn_index" now contains 533 row versions
in 18346 pages
DETAIL: 630 index row versions were removed.
18340 index pages have been deleted, 18339 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pg_largeobject": found 577 removable, 533 nonremovable row
versions in 399 out of 399 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 69015245
There were 550 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.06 s, system: 0.11 s, elapsed: 0.17 s.
INFO: "pg_largeobject": truncated 399 to 305 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: analyzing "pg_catalog.pg_largeobject"
INFO: "pg_largeobject": scanned 305 of 305 pages, containing 533 live
rows and 0 dead rows; 533 rows in sample, 533 estimated total rows
VACUUM
> Are there indexes on this table that perhaps are very large and
> needing to be rescanned many times because maintenance_work_mem
> isn't high enough to handle in a single pass?
On the same healthy instance where we were able to run the VACUUM ANALYZE
VERBOSE, the index size does seem reasonably small:
=> SELECT pg_size_pretty (pg_indexes_size('pg_largeobject'));
-[ RECORD 1 ]--+---
pg_size_pretty | 143 MB
But on the unhealthy instance, it is much larger:
=> SELECT pg_size_pretty (pg_indexes_size('pg_largeobject'));
pg_size_pretty
7241 MB
(1 row)
But it isn't clear to me if the index size is a symptom or if it's the
actual root cause.
> You might try "create index concurrently, drop index concurrently,
> & rename index" (reindex concurrently if you were on
> PG 12) as a sort of online 'vacuum full' on the index(es).
Unfortunately, since pg_largeobject is a system table, the user we use to
connect to the database doesn't have permissions to do this. We get a "
must be owner of relation pg_largeobject" error when we try to create the
replacement index (using CREATE INDEX CONCURRENTLY).
> By the way, the best practices for these mailing list suggest
> partial quoting and responding in-line or below, not "top posting"
> with the entire conversation below.
My mistake! Sorry about that.
Regards,
Jim Hurne
Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked
"Jim Hurne" writes: > On one of the instances that is exhibiting the "disk leak" behavior, the > VACUUM ANALYZE VERBOSE command doesn't generate any output or complete > before I loose the connection to the database (presumably because I hit a > connection read timeout). Is it possible to configure th read timeout for > psql? You could fool with your tcp timeout settings: https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS regards, tom lane
Hiding a GUC from SQL
In my extension pgsodium I'm defining a custom variable at startup to store a key: https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107 I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures the value. This idea was inspired from the pgcryptokey module from Bruce Momjian. The value cannot be shown either with SHOW or current_setting() and it does not appear in pg_settings. From what I can tell, the value is inaccessible from SQL, but I think it's worth asking the experts if there is some other demonstrable way, from SQL, that this value could be leaked even to a superuser. no sql level user should be able to see this value, only a C function, like the pgsodium_derive() from which to derive other keys, should be able to see it. I realize that someone with external process access can get the key, my goal is to prevent accessing it from SQL. Any thoughts on weaknesses to this approach would be welcome. Thanks! -Michel
Re: Hiding a GUC from SQL
Michel Pelletier writes: > In my extension pgsodium I'm defining a custom variable at startup to store > a key: > https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107 > I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE > | GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures the > value. This idea was inspired from the pgcryptokey module from Bruce > Momjian. I guess I'm wondering why you're making it a GUC at all, if you don't want any of the GUC facilities to apply. As far as I can think at the moment, putting in a no-op show hook is sufficient to prevent the value from being seen at the SQL level. However, it's far from clear that doing that isn't going to have negative side-effects; it'll possibly also break other things like GUC save/restore (eg rolling back when a transaction fails). It seems like if you want to be this paranoid, you'd be better off not exposing the variable to the GUC machinery in the first place. You could use a custom set-function (like setseed) to replace the one bit of functionality you do want. regards, tom lane
i am getting issue as FATAL: password authentication failed for user ' postgres' tried
hello team, i have tried every thing but still i could not find the solution to this problem. i made changes in the pg_hba.conf file also , please help me to solve this problem.
Re: i am getting issue as FATAL: password authentication failed for user ' postgres' tried
On 6/17/20 7:14 PM, prachi surangalikar wrote: hello team, i have tried every thing but still i could not find the solution to this problem. i made changes in the pg_hba.conf file also , please help me to solve this problem. What is the connection string you are using when you get the error? What are the settings in the pg_hba.conf file? -- Adrian Klaver adrian.kla...@aklaver.com
Re: i am getting issue as FATAL: password authentication failed for user ' postgres' tried
On 6/17/20 7:14 PM, prachi surangalikar wrote: hello team, i have tried every thing but still i could not find the solution to this problem. i made changes in the pg_hba.conf file also , please help me to solve this problem. Should have added to previous post: Are you sure that you are using the correct password or that the 'postgres' user has a password? -- Adrian Klaver adrian.kla...@aklaver.com
Table partitioning with sequence field in postgresql12
Hi, Partitioning of a table with sequence id as one of its fields is supported in postgresql12? Regards, Seenu.
Re: Table partitioning with sequence field in postgresql12
On Thu, Jun 18, 2020 at 12:00 PM Srinivasa T N wrote: > > Hi, >Partitioning of a table with sequence id as one of its fields is supported > in postgresql12? > Could you please elaborate on your case a bit more? Regards, Amul
Re: Table partitioning with sequence field in postgresql12
Hi,
I have a parent table with one of the field as ""gid" int4 DEFAULT
nextval('"ami_smart_new".aoi_boundary_gid_seq'::regclass)".
I create child tables which inherit parent and use hash partition. When
I directly insert into child tables, will there be any race condition
causing two child tables getting the same sequence value for gid?
Regards,
Seenu.
On Thu, Jun 18, 2020 at 12:09 PM amul sul wrote:
> On Thu, Jun 18, 2020 at 12:00 PM Srinivasa T N wrote:
> >
> > Hi,
> >Partitioning of a table with sequence id as one of its fields is
> supported in postgresql12?
> >
> Could you please elaborate on your case a bit more?
>
> Regards,
> Amul
>
Re: Table partitioning with sequence field in postgresql12
Srinivasa T N writes: > Hi, >Partitioning of a table with sequence id as one of its fields is > supported in postgresql12? > > Regards, > Seenu. A sequence is really just an 'atomic' number generator, you get the next value, which is guaranteed to be larger than the last 'nextval' (up until maxvalue). It is unaware of the use i.e. whether it will be used in a insert or what table that insert is against. So I'm not sure what your concern with a partitioned table is? Can you elaborate? -- Tim Cross
