'Identifier' columns
A couple of questions about auto-assigned identifier columns, forgive my ignorance, I'm used to other methods to create IDs... 1. If creating a new application [i.e. no "legacy" reasons to do anything] using PostgreSQL 10, when creating an "auto-assigned integer ID" column, what are the advantages/disadvantages of using the 'SERIAL' datatype [or equivalent explicitly created SEQUENCE w/ nextval() used as default for column] versus the SQL-standard 'integer GENERATED AS IDENTITY'? All other things being equal, it would seem a no-brainer to follow the standard. 2. When using the SQL-standard 'integer GENERATED AS IDENTITY' column, after inserting a column, what is the recommended method to find the ID of the just-inserted row? Is there no SQL-standard way? The docs seem to imply (without explicitly stating) that a SEQUENCE is used behind the scenes hence 'currval()' could be used, but I didn't see in the docs any mention of what the underlying sequence's name is, or how to specify a name. Perhaps 'lastval()' would work, but not in all cases and in any event it has a sloppy feel to me. Thank you in advance for any advice that can be offered. -- David
Re: Vacuum process waiting on BufferPin
On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote: > On Mon, Aug 13, 2018 at 4:15 PM, Alvaro Herrera > wrote: > >> >> Maybe you had a cursor that was not fully scanned before the session was >> left idle -- as I recall, those can leave buffers pinned. >> > > I don't quite follow this. What circumstances would lead to this situation? > BEGIN WORK; DECLARE CURSOR ... ; FETCH ...; -- for some number of fetches, which does not reach the end of the cursor. then just sit there idle, without having closed the cursor or fetching anything more.
Re: Vacuum process waiting on BufferPin
On Mon, Aug 13, 2018 at 4:04 PM, Don Seiler wrote: > > > Anyway, my next step is getting the OK to terminate those idle in > transaction sessions to see if that gets my vacuum job moving. Meanwhile > I'll ask a dev to sort out why they might be sitting idle in transaction, > there's no reason for them to be unless the app server connection died and > they are zombies. However I'm curious if there is someplace else I > could/should also look to get to the root cause of this. > FYI, killing those idle-in-transaction sessions did free up the vacuum job, which then finished almost immediately afterward. Don. -- Don Seiler www.seiler.us
Re: Vacuum process waiting on BufferPin
On Mon, Aug 13, 2018 at 4:15 PM, Alvaro Herrera wrote: > > Maybe you had a cursor that was not fully scanned before the session was > left idle -- as I recall, those can leave buffers pinned. > I don't quite follow this. What circumstances would lead to this situation? For what its worth, these sessions are backend reporting jobs, not user interfacing at all. Don. -- Don Seiler www.seiler.us
Re: Vacuum process waiting on BufferPin
On 2018-Aug-13, Don Seiler wrote: > This afternoon I discovered an autovacuum process that had been running for > over 6 days. It was waiting on BufferPin event. I kicked off a manual > vacuum+analyze of the table, which automatically killed that autovacuum. > This ran for a few minutes before it too was waiting on a BufferPin event. > I've never witnessed a vacuum session waiting on BufferPin before. Buffer pins are normally released quickly enough. Of course, an idle-in-transaction session has its own problems, but even those would normally not have buffer pins; so vacuum would run to completion without blocking, even if no tuples would be removed. Maybe you had a cursor that was not fully scanned before the session was left idle -- as I recall, those can leave buffers pinned. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Vacuum process waiting on BufferPin
Postgres 9.6.6 on CentOS 7 This afternoon I discovered an autovacuum process that had been running for over 6 days. It was waiting on BufferPin event. I kicked off a manual vacuum+analyze of the table, which automatically killed that autovacuum. This ran for a few minutes before it too was waiting on a BufferPin event. I've never witnessed a vacuum session waiting on BufferPin before. In pg_locks, I see a handful of sessions that have an AccessShareLock on the table I'm trying to vacuum. My vacuum session has a ShareUpdateExclusiveLock on that relation. All of those sessions look like orphaned reporting sessions sitting "idle in transaction". It's unclear to me why a report job would end up idle in transaction, to my knowledge we don't disable autocommit from the reporting app server. Anyway, my next step is getting the OK to terminate those idle in transaction sessions to see if that gets my vacuum job moving. Meanwhile I'll ask a dev to sort out why they might be sitting idle in transaction, there's no reason for them to be unless the app server connection died and they are zombies. However I'm curious if there is someplace else I could/should also look to get to the root cause of this. Don. -- Don Seiler www.seiler.us
Re: JSONB filed with default JSON from a file
On 08/13/2018 12:11 PM, Tom Lane wrote: Merlin Moncure writes: On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru wrote: Thanks! However, this involves writing the entire JSON in the schema file looks inconvenient. I was hoping I would be able to reference to an external JSON file which could be used for the default value. I'm struggling with the notion of default address. Is the point to get a "blank" json structure in place. Perhaps to fill in later? Otherwise, it seems like saying the default name is "Bob"? Rarely the value wanted.
Re: JSONB filed with default JSON from a file
Merlin Moncure writes:
> On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru wrote:
>> Thanks! However, this involves writing the entire JSON in the schema file
>> looks inconvenient. I was hoping I would be able to reference to an external
>> JSON file which could be used for the default value.
> [ put it in a table instead ]
Yeah. If you really insist on having it in a file outside the database,
you can, but you'll need a superuser-privileged function to read it
from that file. Aside from the security aspects, this sort of thing
is an antipattern because it opens you up to backup/restore problems
("oh, we needed that file too?"), replication problems, yadda yadda.
And what are you buying by doing it like that? Better to keep it inside
the DB instead.
regards, tom lane
Re: JSONB filed with default JSON from a file
On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru wrote: >> >> I assume that you could declare the column as >> >> address jsonb not null default 'your json here'::jsonb; > > > Thanks! However, this involves writing the entire JSON in the schema file > looks inconvenient. I was hoping I would be able to reference to an external > JSON file which could be used for the default value. 1) Stick the default json in a table somewhere, say default_json with one row, one column 2) Wrap the table with a function, default_json() that returns the value from the table 3) Make a default function for the table, DEFAULT default_json(). That way you externalize the default into the database merlin
Re: JSONB filed with default JSON from a file
> > I assume that you could declare the column as > > address jsonb not null default 'your json here'::jsonb; > Thanks! However, this involves writing the entire JSON in the schema file looks inconvenient. I was hoping I would be able to reference to an external JSON file which could be used for the default value.
Re: JSONB filed with default JSON from a file
Hi
---
Charles Clavadetscher
Neugasse 84
CH - 8005 Zürich
Tel: +41-79-345 18 88
-
> On 13.08.2018, at 19:40, mrcasa bengaluru wrote:
>
> All,
>
> I'm new to JSONB datatype. We would like to store a nested JSON file in this
> field. Since the JSON is nested, we wanted to create JSON with default value
> from an external JSON file.
>
> My address table looks like,
>
> CREATE TABLE address (
> id CHAR(36) UNIQUE NOT NULL,
> address JSONB NOT NULL
> );
>
> For example, the default JSON will look like,
>
> $ cat address_default.json
>
> {
> "address": {
> "address1": "175 N Street",
> "address2": "Timabktu",
> "location": [
> {
> "city": "Utopia",
> "geolocation": [
> {
> "lat": "12.345",
> "long": "12.1234"
> }
> ],
> "state": "Nowhere"
> }
> ],
> "zip": "96001"
> }
> }
>
>
> How do I make the address_default.json as the default JSON value for the
> address column?
>
I assume that you could declare the column as
address jsonb not null default 'your json here'::jsonb;
I did not try it, but this is what you would do with other data types.
Regards
Charles
JSONB filed with default JSON from a file
All,
I'm new to JSONB datatype. We would like to store a nested JSON file in
this field. Since the JSON is nested, we wanted to create JSON with default
value from an external JSON file.
My address table looks like,
CREATE TABLE address (
id CHAR(36) UNIQUE NOT NULL,
address JSONB NOT NULL
);
For example, the default JSON will look like,
$ cat address_default.json
{
"address": {
"address1": "175 N Street",
"address2": "Timabktu",
"location": [
{
"city": "Utopia",
"geolocation": [
{
"lat": "12.345",
"long": "12.1234"
}
],
"state": "Nowhere"
}
],
"zip": "96001"
}
}
How do I make the address_default.json as the default JSON value for the
address column?
Re: How to get connection details from psql -> \e
On Mon, Aug 13, 2018 at 10:00:56AM -0400, Tom Lane wrote: > The only likely reason I can guess at is that you want vim to make its > own connection to the database for some purpose like autocompletion. That's precisely what I'm looking for. > That's a cute idea, but from a security standpoint it sounds like a > disaster in the making. There isn't any secure way to pass down e.g. > a password (neither command line nor environment variables can be > trusted not to be visible to other users), and even if there was > I'm not sure people would be really happy with handing out their > database credentials to any random piece of code psql invokes. I think that passing all-but-password would be good enough. At the very least for my usecases, as I tend to use pgpass and other non-interactive authentications. Best regards, depesz
Re: How to get connection details from psql -> \e
On Mon, Aug 13, 2018 at 03:32:21PM +0200, Laurenz Albe wrote: > hubert depesz lubaczewski wrote: > > I'm trying to work on some extension to vim when invoked as \e from > > psql. > > > > To make it fully work, I need to know connection details that psql was > > using while it invoked \e. > > > > Is it possible to do in any way, or if not, any chance it could be added > > to wishlist for next versions of Pg? > > Do you mean something like \set? Something like this, but automatically sent "somehow" to editor when I run \e. Best regards, depesz
Re: Fwd: is there any adverse effect on DB if I set autovacuum scale factor to zero?
On 08/13/2018 04:24 PM, Ashu Pachauri wrote: + pgsql-general Thanks and Regards, Ashu Pachauri -- Forwarded message - From: *Ashu Pachauri* mailto:ashu210...@gmail.com>> Date: Mon, Aug 13, 2018 at 7:53 PM Subject: Re: is there any adverse effect on DB if I set autovacuum scale factor to zero? To: mailto:raghavendra...@gmail.com>> The way I see *autovacuum_vacuum_scale_factor* is not in terms of absolute number but as the percentage of any table that can consist of updated / deleted tuples to make it eligible for vacuuming. A factor of 0.1 ensures that your tables would be eligible for vacuuming if more than 10% of the tuples are deleted/updated. 1. If you think that 10% is too high for you in terms of storage cost, you can decrease the number or set it to zero. But, I would advise to increase the value of *autovacuum_vacuum_threshold* to something reasonable if you do that, otherwise you pay the CPU cost frequent vacuuming across all tables. 2. However, if your issue is not the fixed 10% overhead but the lack of throughput i.e. you see the number of deleted/updated tuples keeps increasing in an unbounded fashion, the right way to deal with it is a) Having higher value of *autovacuum_max_workers* b) lower value for *autovacuum_naptime*. Increasing autovacuum_max_workers is unlikely to solve the issue with throughput, because all the workers are throttled together - there's a limit on the amount of work that can be done per second. Increasing the number of workers is akin to allowing more cars on a highway, but also lowering the speed limit. You need to increase the limit on amount of work, and lowering naptime is one way to do that. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Fwd: is there any adverse effect on DB if I set autovacuum scale factor to zero?
+ pgsql-general Thanks and Regards, Ashu Pachauri -- Forwarded message - From: Ashu Pachauri Date: Mon, Aug 13, 2018 at 7:53 PM Subject: Re: is there any adverse effect on DB if I set autovacuum scale factor to zero? To: The way I see *autovacuum_vacuum_scale_factor* is not in terms of absolute number but as the percentage of any table that can consist of updated / deleted tuples to make it eligible for vacuuming. A factor of 0.1 ensures that your tables would be eligible for vacuuming if more than 10% of the tuples are deleted/updated. 1. If you think that 10% is too high for you in terms of storage cost, you can decrease the number or set it to zero. But, I would advise to increase the value of *autovacuum_vacuum_threshold* to something reasonable if you do that, otherwise you pay the CPU cost frequent vacuuming across all tables. 2. However, if your issue is not the fixed 10% overhead but the lack of throughput i.e. you see the number of deleted/updated tuples keeps increasing in an unbounded fashion, the right way to deal with it is a) Having higher value of *autovacuum_max_workers* b) lower value for *autovacuum_naptime*. Apart from configuration tuning, one common reason for low vacuum throughput is lock waits. You can turn on *log_lock_waits* config to find out if that's what's happening. As a general rule of thumb, you should not have long running transactions, especially the ones that require *share/share row exclusive/ exclusive /access exclusive* locks. They not only hamper vacuuming throughput but also the throughput of your db writes in general. Thanks and Regards, Ashu Pachauri On Mon, Aug 13, 2018 at 7:11 PM Raghavendra Rao J S V < raghavendra...@gmail.com> wrote: > > Hi Tomas, > > Thank you very much for your response. > > As we know table becomes a candidate for autovacuum process based on > below formula. > > > *Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * > number of tuples + autovacuum_vacuum_threshold* > > > > *Current settings in my database are as follows.* > > > *autovacuum_vacuum_scale_factor = 0.1 * > > *autovacuum_vacuum_threshold = 40* > > > > Due to above formula the dead tuples are accumulating based on the number > of live tuples as show below picture. > > > select relname,n_live_tup,n_dead_tup,(n_live_tup*.1+40) > expected_to_autovacuum,* from pg_stat_user_tables > where n_dead_tup>800 > order by n_live_tup desc > limit 100; > > > > > In order to avoid the dead tuples accumulation I wold like to change the > auto vacuum settings in *"postgresql.conf"* as below. > > *autovacuum_vacuum_scale_factor = 0.01* > > * autovacuum_vacuum_threshold = 100* > > > *Kindly guide me your views. Does it cause any adverse effect on DB.* > > Regards, > Raghavendra Rao > > > > On 13 August 2018 at 18:05, Tomas Vondra > wrote: > >> >> >> On 08/13/2018 11:07 AM, Raghavendra Rao J S V wrote: >> >>> Hi All, >>> >>> We are using postgres *9.2* version on *Centos *operating system. We >>> have around *1300+* tables.We have following auto vacuum settings are >>> enables. Still few of the tables(84 tables) which are always busy are not >>> vacuumed.Dead tuples in those tables are more than 5000. Due to that >>> tables are bloating and observed few areas has performance degradation. >>> >>> >> You don't say how large the tables are, so it's impossible to say whether >> 5000 dead tuples is excessive or not. IMHO it's a negligible amount and >> should not lead to excessive bloat or issues. >> >> A certain amount of wasted is expected - it's a trade-off between >> immediate and delayed cleanup. If you delay the cleanup a bit, it's going >> to be more efficient overall. >> >> It's also unclear why the tables are not vacuumed - it may easily be due >> to all the autovacuum workers being constantly busy, unable to cleanup all >> tables in a timely manner. In that case lowering the threshold is not going >> to help, on the contrary. >> >> regards >> >> -- >> Tomas Vondra http://www.2ndQuadrant.com >> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >> > > > > -- > Regards, > Raghavendra Rao J S V > Mobile- 8861161425 >
Re: Replication failure, slave requesting old segments
Adrian Klaver wrote: "If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments. *This is dependent on verification that the archiving is working properly. A belt and suspenders approach would be to set wal_keep_segments to a value > 0 in the event archiving is not properly functioning*" " Adrian, I believe that the suggestion that my issue was the result of my archiving process not working is not correct. The quote above does not address the requirement for wal_keep_segments to be >= 1 even when archiving is functioning correctly. I will continue to monitor this thread in the hope that others will confirm my understanding, but otherwise I will bow out now (and file a bug). Thanks to all. Phil.
Re: Replication failure, slave requesting old segments
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 08/13/2018 05:08 AM, Phil Endecott wrote: > >Adrian Klaver wrote: > >Really? I thought the intention was that the system should be > >able to recover reliably when the slave reconnects after a > >period of downtime, subject only to there being sufficient > >network/CPU/disk bandwidth etc. for it to eventually catch up. That's correct. > See also my reply to Stephen earlier. Basically you are trying to coordinate > two different operations. They start from the same source pg_xlog(pg_wal > 10+) but arrive on a different time scale and from different locations. > Without sufficient sanity checks it is possible they diverge enough on one > or both paths to render the process unstable. This isn't what's happening. We're not talking about a timeline change here or a replica being promoted to be a primary in general. There's no diverging happening- it's the same consistent WAL stream, just coming from two different sources, which PG is specifically designed to handle and should be handling seamlessly. > I would say that: > > "If you set up a WAL archive that's accessible from the standby, these > solutions are not required, since the standby can always use the archive to > catch up provided it retains enough segments." > > should be more like: > > "If you set up a WAL archive that's accessible from the standby, these > solutions are not required, since the standby can always use the archive to > catch up provided it retains enough segments. *This is dependent on > verification that the archiving is working properly. A belt and suspenders > approach would be to set wal_keep_segments to a value > 0 in the event > archiving is not properly functioning*" > " I don't think I can disagree more with this additional wording, and I *really* don't think we should be encouraging people to set a high wal_keep_segments. The specific case here looks like it just need to be set to, exactly, '1', to ensure that the primary hasn't removed the last WAL file that it archived. Thanks! Stephen signature.asc Description: PGP signature
Re: Replication failure, slave requesting old segments
On 08/13/2018 05:08 AM, Phil Endecott wrote: Adrian Klaver wrote: On 08/12/2018 02:56 PM, Phil Endecott wrote: Anyway. Do others agree that my issue was the result of wal_keep_segments=0 ? Only as a sub-issue of the slave losing contact with the master. The basic problem is maintaining two separate operations, archiving and streaming, in sync. If either or some combination of both lose synchronization then it is anyone's guess on what is appropriate for wal_keep_segments. Really? I thought the intention was that the system should be able to recover reliably when the slave reconnects after a period of downtime, subject only to there being sufficient network/CPU/disk bandwidth etc. for it to eventually catch up. See also my reply to Stephen earlier. Basically you are trying to coordinate two different operations. They start from the same source pg_xlog(pg_wal 10+) but arrive on a different time scale and from different locations. Without sufficient sanity checks it is possible they diverge enough on one or both paths to render the process unstable. If that's not true, I think the docs need an even more extensive overhaul! Suggestion for the paragraph that I quoted before from 26.2.5: "If you set up a WAL archive that's accessible from the standby, it's anyone's guess what is appropriate for wal_keep_segments." I would say that: "If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments." should be more like: "If you set up a WAL archive that's accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments. *This is dependent on verification that the archiving is working properly. A belt and suspenders approach would be to set wal_keep_segments to a value > 0 in the event archiving is not properly functioning*" " Regards, Phil. -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to get connection details from psql -> \e
hubert depesz lubaczewski writes: > I'm trying to work on some extension to vim when invoked as \e from > psql. > To make it fully work, I need to know connection details that psql was > using while it invoked \e. Uh, why? The only likely reason I can guess at is that you want vim to make its own connection to the database for some purpose like autocompletion. That's a cute idea, but from a security standpoint it sounds like a disaster in the making. There isn't any secure way to pass down e.g. a password (neither command line nor environment variables can be trusted not to be visible to other users), and even if there was I'm not sure people would be really happy with handing out their database credentials to any random piece of code psql invokes. > Is it possible to do in any way, or if not, any chance it could be added > to wishlist for next versions of Pg? You'd need to make a case for it that's a lot stronger than "I wish" before anyone would consider this. regards, tom lane
Re: is there any adverse effect on DB if I set autovacuum scale factor to zero?
On 08/13/2018 03:41 PM, Raghavendra Rao J S V wrote: Hi Tomas, Thank you very much for your response. As we know table becomes a candidate for autovacuum process based on below formula. *Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold* *Current settings in my database are as follows.* /autovacuum_vacuum_scale_factor = 0.1/ /autovacuum_vacuum_threshold = 40/ Due to above formula the dead tuples are accumulating based on the number of live tuples as show below picture. select relname,n_live_tup,n_dead_tup,(n_live_tup*.1+40) expected_to_autovacuum,* from pg_stat_user_tables where n_dead_tup>800 order by n_live_tup desc limit 100; In order to avoid the dead tuples accumulation I wold like to change the auto vacuum settings in *"postgresql.conf"* as below. /autovacuum_vacuum_scale_factor = 0.01/ //autovacuum_vacuum_threshold = 100/ / OK, so the tables apparently have enough dead tuples to trigger vacuum. That mean the autovacuum throughput is insufficient to do all the cleanup. If you lower the scale factor, the amount of cleanup will *increase* (more tables being eligible for cleanup) making it less likely autovacuum can keep up. You need to increase the throughtput, by increasing vacuum_cost_limit or something like that. *Kindly guide me your views. Does it cause any adverse effect on DB.* * Well, it forces the database to do more stuff / more often, so it may have adverse impact, of course. It's hard to say if it's going to be a win overall, because we don't know how serious is the bloat. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Replication failure, slave requesting old segments
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 08/13/2018 05:39 AM, Stephen Frost wrote: > >* Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: > >>Adrian Klaver wrote: > >>>On 08/12/2018 02:56 PM, Phil Endecott wrote: > Anyway. Do others agree that my issue was the result of > wal_keep_segments=0 ? > >>> > >>>Only as a sub-issue of the slave losing contact with the master. The basic > >>>problem is maintaining two separate operations, archiving and streaming, > >>>in sync. If either or some combination of both lose synchronization then > >>>it is anyone's guess on what is appropriate for wal_keep_segments. > > > >Uh, no, having an archive_command and a restore_command configures > >exactly should remove the need to worry about what wal_keep_segments is > >set to because anything not on the primary really should be available > >through what's been archived and PG shouldn't have any trouble figuring > >that out and working with it. > > > >If all you've got is streaming replication then, sure, you have no idea > >what to set wal_keep_segments to because the replica could be offline > >for an indeterminate amount of time, but as long as you're keeping track > >of all the WAL through archive_command, that shouldn't be an issue. > > Therein lies the rub. As I stated previously the bigger issue is syncing two > different operations, archiving and streaming. That's not correct though, there isn't a big issue regarding syncing of those two operations. > The OP got caught short > assuming the archiving would handle the situation where the streaming was > down for a period. In his particular setup and for this particular situation > a wal_keep_segments of 1 would have helped. I do not see this as a default > value though as it depends on too many variables outside the reach of the > database, mostly notably the success of the archive command. What's been pointed out here is that even if everything is working (archive_command, restore_command, etc) there's a possible gap in the transisition from replay-from-archive and starting streaming replication where the replica might not be able to start streaming. That's an entirely *PostgreSQL* issue, as far as I see it, and hasn't got anything to do with his particular setup except that he managed to expose the issue. > First is the > command even valid, two is the network link reliable, three is there even a > network link, is there more then one network link, four is the restore > command valid? That is just of the top of my head, more caffeine and I could > come up with more. Saying that having archiving, streaming and a > wal_keep_segments=1 has you covered, is misleading. I don't see it as > detrimental to performance but I do see more posts down the road from folks > who are surprised when it does not cover their case. Personally I think it > better to be up front that this requires more thought or a third party > solution that has done the thinking. This is all down to "and you should also monitor to make sure things continue working" which I certainly agree with but that doesn't mean we shouldn't fix this issue. This entire side-discussion feels like it's really off in the weeds. The next steps which I outlined a while ago seem to still be entirely appropriate and we should figure out a way to solve this issue so that, when everything else is working (archive command, restore command, replica is able to connect to the primary, etc), that PG behaves sanely and is able to catch up with and connect to the primary and resume streaming. Thanks! Stephen signature.asc Description: PGP signature
Re: Replication failure, slave requesting old segments
On 08/13/2018 05:39 AM, Stephen Frost wrote: Greetings, * Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: Adrian Klaver wrote: On 08/12/2018 02:56 PM, Phil Endecott wrote: Anyway. Do others agree that my issue was the result of wal_keep_segments=0 ? Only as a sub-issue of the slave losing contact with the master. The basic problem is maintaining two separate operations, archiving and streaming, in sync. If either or some combination of both lose synchronization then it is anyone's guess on what is appropriate for wal_keep_segments. Uh, no, having an archive_command and a restore_command configures exactly should remove the need to worry about what wal_keep_segments is set to because anything not on the primary really should be available through what's been archived and PG shouldn't have any trouble figuring that out and working with it. If all you've got is streaming replication then, sure, you have no idea what to set wal_keep_segments to because the replica could be offline for an indeterminate amount of time, but as long as you're keeping track of all the WAL through archive_command, that shouldn't be an issue. Therein lies the rub. As I stated previously the bigger issue is syncing two different operations, archiving and streaming. The OP got caught short assuming the archiving would handle the situation where the streaming was down for a period. In his particular setup and for this particular situation a wal_keep_segments of 1 would have helped. I do not see this as a default value though as it depends on too many variables outside the reach of the database, mostly notably the success of the archive command. First is the command even valid, two is the network link reliable, three is there even a network link, is there more then one network link, four is the restore command valid? That is just of the top of my head, more caffeine and I could come up with more. Saying that having archiving, streaming and a wal_keep_segments=1 has you covered, is misleading. I don't see it as detrimental to performance but I do see more posts down the road from folks who are surprised when it does not cover their case. Personally I think it better to be up front that this requires more thought or a third party solution that has done the thinking. Really? I thought the intention was that the system should be able to recover reliably when the slave reconnects after a period of downtime, subject only to there being sufficient network/CPU/disk bandwidth etc. for it to eventually catch up. Yes, that's correct, the replica should always be able to catch back up presuming there's no gaps in the WAL between when the replica failed and where the primary is at. Thanks! Stephen -- Adrian Klaver adrian.kla...@aklaver.com
Re: is there any adverse effect on DB if I set autovacuum scale factor to zero?
Hi Tomas, Thank you very much for your response. As we know table becomes a candidate for autovacuum process based on below formula. *Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold* *Current settings in my database are as follows.* *autovacuum_vacuum_scale_factor = 0.1 * *autovacuum_vacuum_threshold = 40* Due to above formula the dead tuples are accumulating based on the number of live tuples as show below picture. select relname,n_live_tup,n_dead_tup,(n_live_tup*.1+40) expected_to_autovacuum,* from pg_stat_user_tables where n_dead_tup>800 order by n_live_tup desc limit 100; In order to avoid the dead tuples accumulation I wold like to change the auto vacuum settings in *"postgresql.conf"* as below. *autovacuum_vacuum_scale_factor = 0.01* * autovacuum_vacuum_threshold = 100* *Kindly guide me your views. Does it cause any adverse effect on DB.* Regards, Raghavendra Rao On 13 August 2018 at 18:05, Tomas Vondra wrote: > > > On 08/13/2018 11:07 AM, Raghavendra Rao J S V wrote: > >> Hi All, >> >> We are using postgres *9.2* version on *Centos *operating system. We >> have around *1300+* tables.We have following auto vacuum settings are >> enables. Still few of the tables(84 tables) which are always busy are not >> vacuumed.Dead tuples in those tables are more than 5000. Due to that >> tables are bloating and observed few areas has performance degradation. >> >> > You don't say how large the tables are, so it's impossible to say whether > 5000 dead tuples is excessive or not. IMHO it's a negligible amount and > should not lead to excessive bloat or issues. > > A certain amount of wasted is expected - it's a trade-off between > immediate and delayed cleanup. If you delay the cleanup a bit, it's going > to be more efficient overall. > > It's also unclear why the tables are not vacuumed - it may easily be due > to all the autovacuum workers being constantly busy, unable to cleanup all > tables in a timely manner. In that case lowering the threshold is not going > to help, on the contrary. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- Regards, Raghavendra Rao J S V Mobile- 8861161425
Re: How to get connection details from psql -> \e
hubert depesz lubaczewski wrote: > I'm trying to work on some extension to vim when invoked as \e from > psql. > > To make it fully work, I need to know connection details that psql was > using while it invoked \e. > > Is it possible to do in any way, or if not, any chance it could be added > to wishlist for next versions of Pg? Do you mean something like \set? test=> \set DBNAME = 'test' ENCODING = 'UTF8' HOST = '/var/run/postgresql' PORT = '5432' SERVER_VERSION_NAME = '10.5' SERVER_VERSION_NUM = '15' VERSION_NAME = '10.5' VERSION_NUM = '15' ... Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Replication failure, slave requesting old segments
Greetings, * Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: > Adrian Klaver wrote: > >On 08/12/2018 02:56 PM, Phil Endecott wrote: > >>Anyway. Do others agree that my issue was the result of > >>wal_keep_segments=0 ? > > > >Only as a sub-issue of the slave losing contact with the master. The basic > >problem is maintaining two separate operations, archiving and streaming, > >in sync. If either or some combination of both lose synchronization then > >it is anyone's guess on what is appropriate for wal_keep_segments. Uh, no, having an archive_command and a restore_command configures exactly should remove the need to worry about what wal_keep_segments is set to because anything not on the primary really should be available through what's been archived and PG shouldn't have any trouble figuring that out and working with it. If all you've got is streaming replication then, sure, you have no idea what to set wal_keep_segments to because the replica could be offline for an indeterminate amount of time, but as long as you're keeping track of all the WAL through archive_command, that shouldn't be an issue. > Really? I thought the intention was that the system should be > able to recover reliably when the slave reconnects after a > period of downtime, subject only to there being sufficient > network/CPU/disk bandwidth etc. for it to eventually catch up. Yes, that's correct, the replica should always be able to catch back up presuming there's no gaps in the WAL between when the replica failed and where the primary is at. Thanks! Stephen signature.asc Description: PGP signature
Re: is there any adverse effect on DB if I set autovacuum scale factor to zero?
On 08/13/2018 11:07 AM, Raghavendra Rao J S V wrote: Hi All, We are using postgres *9.2* version on *Centos *operating system. We have around *1300+* tables.We have following auto vacuum settings are enables. Still few of the tables(84 tables) which are always busy are not vacuumed.Dead tuples in those tables are more than 5000. Due to that tables are bloating and observed few areas has performance degradation. You don't say how large the tables are, so it's impossible to say whether 5000 dead tuples is excessive or not. IMHO it's a negligible amount and should not lead to excessive bloat or issues. A certain amount of wasted is expected - it's a trade-off between immediate and delayed cleanup. If you delay the cleanup a bit, it's going to be more efficient overall. It's also unclear why the tables are not vacuumed - it may easily be due to all the autovacuum workers being constantly busy, unable to cleanup all tables in a timely manner. In that case lowering the threshold is not going to help, on the contrary. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Replication failure, slave requesting old segments
Greetings, * Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: > Adrian Klaver wrote: > >On 08/12/2018 03:54 PM, Stephen Frost wrote: > >>Greetings, > >> > >>* Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: > >>>OK. I think this is perhaps a documentation bug, maybe a missing > >>>warning when the master reads its configuration, and maybe (as you say) > >>>a bad default value. > >> > >>If we consider it to be an issue worthy of a change then we should > >>probably just change the default value, and maybe not even allow it to > >>be set lower than '1'. > > > >I would say leave the default at 0 as it leaves no doubt that you are > >performing without a net. A setting of '1' implies you are covered and for > >a fast moving cluster or slow moving one with sufficient downtime that > >would not be the case. > > Can you explain how it can fail in the case of a "slow moving cluster with > sufficient downtime"? I'm guessing 'sufficient downtime' here is, basically, 'offline until the next checkpoint', which isn't actually all *that* much time. > It seems to me that if I have correctly understood what happened in this > case then 0, the default, really cannot ever work properly when you have > enabled WAL archiving plus streaming. Well, it's not like it'd work without WAL archiving either, though that's perhaps more obviously true. Thanks! Stephen signature.asc Description: PGP signature
Re: Replication failure, slave requesting old segments
Adrian Klaver wrote: On 08/12/2018 03:54 PM, Stephen Frost wrote: Greetings, * Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: OK. I think this is perhaps a documentation bug, maybe a missing warning when the master reads its configuration, and maybe (as you say) a bad default value. If we consider it to be an issue worthy of a change then we should probably just change the default value, and maybe not even allow it to be set lower than '1'. I would say leave the default at 0 as it leaves no doubt that you are performing without a net. A setting of '1' implies you are covered and for a fast moving cluster or slow moving one with sufficient downtime that would not be the case. Can you explain how it can fail in the case of a "slow moving cluster with sufficient downtime"? It seems to me that if I have correctly understood what happened in this case then 0, the default, really cannot ever work properly when you have enabled WAL archiving plus streaming. Better to let the end user know this is not a simple problem and some thought needs to go into configuration. I certainly agree that this is "not a simple problem", having read something like 18,000 words of documentation multiple times and, apparently, still got it wrong in multiple ways. Regards, Phil.
Re: Replication failure, slave requesting old segments
Adrian Klaver wrote: On 08/12/2018 02:56 PM, Phil Endecott wrote: Anyway. Do others agree that my issue was the result of wal_keep_segments=0 ? Only as a sub-issue of the slave losing contact with the master. The basic problem is maintaining two separate operations, archiving and streaming, in sync. If either or some combination of both lose synchronization then it is anyone's guess on what is appropriate for wal_keep_segments. Really? I thought the intention was that the system should be able to recover reliably when the slave reconnects after a period of downtime, subject only to there being sufficient network/CPU/disk bandwidth etc. for it to eventually catch up. If that's not true, I think the docs need an even more extensive overhaul! Suggestion for the paragraph that I quoted before from 26.2.5: "If you set up a WAL archive that's accessible from the standby, it's anyone's guess what is appropriate for wal_keep_segments." Regards, Phil.
Re: Replication failure, slave requesting old segments
Greetings, * Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 08/12/2018 03:54 PM, Stephen Frost wrote: > >* Phil Endecott (spam_from_pgsql_li...@chezphil.org) wrote: > >>OK. I think this is perhaps a documentation bug, maybe a missing > >>warning when the master reads its configuration, and maybe (as you say) > >>a bad default value. > > > >If we consider it to be an issue worthy of a change then we should > >probably just change the default value, and maybe not even allow it to > >be set lower than '1'. > > I would say leave the default at 0 as it leaves no doubt that you are > performing without a net. A setting of '1' implies you are covered and for a > fast moving cluster or slow moving one with sufficient downtime that would > not be the case. Better to let the end user know this is not a simple > problem and some thought needs to go into configuration. Uh, this specific case is where there *is* a 'safety net' though- archive command and restore command were configured and being used, so I don't buy off on this argument at all. Maybe we just internally bump wal_keep_segments to '1' to avoid this specific risk without actually changing the default or making people change their existing configurations, but if this is really what's happening then I don't think the answer is "don't do anything." Thanks! Stephen signature.asc Description: PGP signature
How to get connection details from psql -> \e
Hi, I'm trying to work on some extension to vim when invoked as \e from psql. To make it fully work, I need to know connection details that psql was using while it invoked \e. Is it possible to do in any way, or if not, any chance it could be added to wishlist for next versions of Pg? Best regards, depesz
Re: Query: Migrating from SQLServer to Postgresql
Complete URL for npgsql is : https://github.com/npgsql/npgsql On Mon, Aug 13, 2018 at 2:43 PM Sachin Kotwal wrote: > Hi > > On Mon, Aug 13, 2018 at 1:37 PM Darnie Graceline > wrote: > >> Hi, >> Iam looking into a process of migrating from SQLServer to Postgresql, and >> I see that DBMigration is one of the tools you have offered, however when >> analysed DBMigration does not support migrating all components under free >> version. >> >> 1.Could you help suggest a free tool that we can use to migrate all the >> components from SQLServer DB to Postgresql. >> >> Not having experience on actual migration but below links may help you: > > > https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL > > https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding > > https://github.com/dalibo/sqlserver2pgsql > https://sourceforge.net/projects/mssql2pgsql/ > https://www.devbridge.com/articles/migrating-from-mssql-to-postgresql/# > > http://www.sqlines.com/sql-server-to-postgresql-tool > https://dbconvert.com/mssql/postgresql/ > > Search for migration tool at : > https://www.postgresql.org/download/products/1/ > > 2. Also could you suggest the best suitable driver while communicating >> from .Net to Postgresql. >> >> For .Net connection PostgreSQL used npgsql drivers. > https://github.com/npgsql/npg > > Thanks & Regards, >> Darnie. >> >> >> >> > > -- > > Thanks and Regards, > Sachin Kotwal > -- Thanks and Regards, Sachin Kotwal
Re: Query: Migrating from SQLServer to Postgresql
Hi On Mon, Aug 13, 2018 at 1:37 PM Darnie Graceline wrote: > Hi, > Iam looking into a process of migrating from SQLServer to Postgresql, and > I see that DBMigration is one of the tools you have offered, however when > analysed DBMigration does not support migrating all components under free > version. > > 1.Could you help suggest a free tool that we can use to migrate all the > components from SQLServer DB to Postgresql. > > Not having experience on actual migration but below links may help you: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding https://github.com/dalibo/sqlserver2pgsql https://sourceforge.net/projects/mssql2pgsql/ https://www.devbridge.com/articles/migrating-from-mssql-to-postgresql/# http://www.sqlines.com/sql-server-to-postgresql-tool https://dbconvert.com/mssql/postgresql/ Search for migration tool at : https://www.postgresql.org/download/products/1/ 2. Also could you suggest the best suitable driver while communicating from > .Net to Postgresql. > > For .Net connection PostgreSQL used npgsql drivers. https://github.com/npgsql/npg Thanks & Regards, > Darnie. > > > > -- Thanks and Regards, Sachin Kotwal
is there any adverse effect on DB if I set autovacuum scale factor to zero?
Hi All, We are using postgres *9.2* version on *Centos *operating system. We have around *1300+* tables.We have following auto vacuum settings are enables. Still few of the tables(84 tables) which are always busy are not vacuumed.Dead tuples in those tables are more than 5000. Due to that tables are bloating and observed few areas has performance degradation. autovacuum = on log_autovacuum_min_duration = 100 autovacuum_max_workers = 5 autovacuum_naptime = 1min autovacuum_vacuum_threshold = 40 autovacuum_analyze_threshold = 20 autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.05 autovacuum_freeze_max_age = 2 autovacuum_vacuum_cost_delay = 30ms autovacuum_vacuum_cost_limit = 1200 # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0ms # 0-100 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits vacuum_cost_limit = 200 # 1-1 credits In order to avoid the table bloating and performance degradation,we would like to set the* ' autovacuum_vacuum_scale_factor'(zero) * and *'autovacuum_vacuum_threshold ' (200)* settings for the busy tables as below. Please let me know is there any adverse effect on DB if I set autovacuum scale factor to zero for certain tables. If yes, what is the effect and how to test. ALTER TABLE cmdevice SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 200); Kindly let me know the role of *autovacuum_vacuum_cost_delay* and *autovacuum_vacuum_cost_limit* settings . Regards, Raghavendra Rao
Query: Migrating from SQLServer to Postgresql
Hi, Iam looking into a process of migrating from SQLServer to Postgresql, and I see that DBMigration is one of the tools you have offered, however when analysed DBMigration does not support migrating all components under free version. 1.Could you help suggest a free tool that we can use to migrate all the components from SQLServer DB to Postgresql. 2. Also could you suggest the best suitable driver while communicating from .Net to Postgresql. Thanks & Regards, Darnie. signature.asc Description: PGP signature
Re: Safe operations?
Thanks everyone for your prompt help. It sounds like a rename operation is almost never an issue unless you literally had millions of indexes. Thanks for all the follow on questions and answers, it was most helpful and interesting to learn a bit more about PG internals. On Mon, 13 Aug 2018 at 12:01, Tom Lane wrote: > Tim Cross writes: > > On Mon, 13 Aug 2018 at 12:23, Olivier Gautherot > >> On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross > wrote: > >>> Just wondering - what about the case when the column being renamed is > >>> also referenced in an index or check constraint? > > >> Tim, as far as I know, names are only an attribute tagged to an OID. > >> Internal relations are though these OIDs, not names, so renaming a > column > >> is really one-shot. Names are mainly a more convenient way of referring > to > >> objects. > > > thanks Olivier, that is what I suspected and your explanation fits with > my > > mental model. I had assumed table/column names are convenience for humans > > and that the system would use OIDs etc for internal references. > > Right, catalog internal references are all via OIDs or column numbers, > so that the only thing the system thinks it needs to do is update the > "name" field in a single catalog row. (A problem with this is that > user-defined function bodies are stored as text; so you may well have > to run around and fix your functions by hand. But that doesn't > contribute to the cost of the RENAME operation per se.) > > Getting back to Samuel's original question, the reason we don't try > to document performance issues like this is that there are just too > many moving parts. Yeah, the update of the catalog row should be > more or less O(1), and then the required updates of the catalog's > indexes will be more or less O(log N) (N being the number of rows > in that catalog). But in practice the main constraint is often the > need to obtain locks on the relevant database objects, and that's > really hard to give a prediction for. > > regards, tom lane >
