Re: [pgbackrest] Expiring the last backup?

On 1/25/19 8:02 AM, Ron wrote: On 1/24/19 11:22 PM, David Steele wrote: On 1/24/19 5:07 PM, Ron wrote: Are these the steps, or am I missing something? $ pgbackrest stop $ pgbackrest stanza-delete --stanza=mystanza--force That looks right but no need for --force.  That's what the `stop` is

Re: [External] Re: Geographical multi-master replication

I do not know the use case but we did try the following. We had a small requirement wrt some regional data written to tables but needs to be available to all regions. We made use of logical replication to replicate/publish each local table to all the other regions ( like a many to many) In theory,

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 11:22 PM, David Steele wrote: On 1/24/19 5:07 PM, Ron wrote: Are these the steps, or am I missing something? $ pgbackrest stop $ pgbackrest stanza-delete --stanza=mystanza--force That looks right but no need for --force.  That's what the `stop` is for -- to let pgBackRest know yo

Re: Geographical multi-master replication

Am 25.01.19 um 06:10 schrieb Jeremy Finzel: The problem is that the version for BDR 1.0.7, which has an implementation for postgres 9.4, will be on end of live at the end of this year. Unfortunately the paid solution is out of our budget, so we currently have two options: find

Re: Casting Integer to Boolean in assignment

Alexandre GRAIL wrote: > On 24/01/2019 12:45, Geoff Winkless wrote: > > My own opinion is that non-0 should implicitly cast as true and 0 > > should cast as false. I just run > > > > UPDATE pg_cast SET castcontext = 'i' WHERE oid IN ( > > SELECT c.oid > > FROM pg_cast c > > inner join pg_typ

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 5:07 PM, Ron wrote: Are these the steps, or am I missing something? $ pgbackrest stop $ pgbackrest stanza-delete --stanza=mystanza--force That looks right but no need for --force. That's what the `stop` is for -- to let pgBackRest know you really mean to do this. See documentat

Re: Geographical multi-master replication

> > The problem is that the version for BDR 1.0.7, which has an implementation > for postgres 9.4, will be on end of live at the end of this year. > Unfortunately the paid solution is out of our budget, so we currently have > two options: find an alternative or remove the multi-region implementatio

Geographical multi-master replication

Hi Everybody, I am currently working with a client that has requirements for geographically dispersed multi-master replication. The current solution that we have in place is the BDR 1.0.7, which is asynchronous and eventually consistent and it is actually very close to real time. So far we are ma

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Hi Tom. It's 9.5 latest. I'll try pgdump tomorrow. Obter o Outlook para Android From: Tom Lane Sent: Thursday, January 24, 2019 9:02:25 PM To: Duarte Carreira Cc: pgsql-gene...@postgresql.org Subject: Re: duplicate OID issue when using pg

Re: log_min_duration_statement

On 1/24/19 7:52 AM, bhargav kamineni wrote: Hi Team,, >The above would not happen to be autovacuum statements? Nope, those are not autovaccum related logs , they are related to queries only. On the chance that this setting is being overridden somewhere what does the below show when run

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira writes: > I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this > issue... 9.5.what? Perusing the commit logs, I note that 9.5.3 included a fix for a pg_upgrade issue that could possibly lead to this symptom, see https://git.postgresql.org/gitweb/?p=postg

duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Hello. I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this issue... I've found a similar thread, but going from 8.4 to 9.2: https://www.postgresql.org/message-id/flat/52C44FA9.2080500%40gmail.com#14ab24f6c94287dd4c435652cb2d77f8 This ended up without really pinpointing

Re: Recommended Hardware requirements for PostgreSQL DB Server.

On 1/24/19 12:06 PM, Kaushal Shriyan wrote: Hi, Are there any recommended hardware requirements to setup PostgreSQL DB server?  I checked https://wiki.postgresql.org/wiki/Database_Hardware but unable to find it. I am going to install Postgresql 9.6 version in AWS (https://aws.amazon.com/ec2/i

Recommended Hardware requirements for PostgreSQL DB Server.

Hi, Are there any recommended hardware requirements to setup PostgreSQL DB server? I checked https://wiki.postgresql.org/wiki/Database_Hardware but unable to find it. I am going to install Postgresql 9.6 version in AWS ( https://aws.amazon.com/ec2/instance-types/). I will appreciate if someone ca

Re: Casting Integer to Boolean in assignment

On 24/01/2019 17:19, Geoff Winkless wrote: Like you say it is a matter of opinion. The projects opinion is here: https://www.postgresql.org/docs/11/datatype-boolean.html and it works for me. And you're welcome to it. I'm not arguing for it changing. I'm simply stating that I'm very pleased th

Re: Casting Integer to Boolean in assignment

On 24/01/2019 12:45, Geoff Winkless wrote: My own opinion is that non-0 should implicitly cast as true and 0 should cast as false. I just run UPDATE pg_cast SET castcontext = 'i' WHERE oid IN ( SELECT c.oid FROM pg_cast c inner join pg_type src ON src.oid = c.castsource inner join pg_typ

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 16:00, Adrian Klaver wrote: > People are going to make mistakes that is a given. Eliminating a boolean > test is not going to change that. I still think that if you've got to the point where you're actually part-way through writing a clause you're unlikely to forget to comp

Re: Casting Integer to Boolean in assignment

On 1/24/19 7:48 AM, Geoff Winkless wrote: On Thu, 24 Jan 2019 at 15:40, Adrian Klaver wrote: delete from delete_test where and then forget the 'field =' part. Though my more common mistake along that line is: delete from delete_test; At any rate, if it can be done it will be done. If you f

Re: log_min_duration_statement

> Hi Team,, > > I have set log_min_duration_statement=1000 as my configuration but when > i observe log file i could see queries that ran below 1000ms are also > getting logged , for instance > duration: 0.089 ms > duration: 0.175 ms > duration: 0.139 ms > duration: 0.451 ms > duration: 0.136

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:40, Adrian Klaver wrote: > delete from delete_test where > > and then forget the 'field =' part. Though my more common mistake along > that line is: > > delete from delete_test; > > At any rate, if it can be done it will be done. If you follow that logic, then having a s

Re: Casting Integer to Boolean in assignment

On 1/24/19 7:32 AM, Geoff Winkless wrote: On Thu, 24 Jan 2019 at 15:28, Adrian Klaver wrote: On 1/24/19 7:21 AM, Geoff Winkless wrote: How could you even write a query like the one Thomas posted? It doesn't even look remotely sensible. delete from delete_test where 1::boolean; *chuckle*

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:32, Geoff Winkless wrote: > DELETE FROM WHERE ; > > What would you be thinking that that ought to do? To be fair, I suppose that accidentally missing out a test but including an integer field DELETE FROM WHERE ; could do this. Not something I've ever done, but at lea

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:28, Adrian Klaver wrote: > On 1/24/19 7:21 AM, Geoff Winkless wrote: > > How could you even write a query like the one Thomas posted? It > > doesn't even look remotely sensible. > delete from delete_test where 1::boolean; *chuckle* You misunderstand me. I mean, how ca

Re: How duplicate values inserted into the primary key column of table and how to fix it

On 1/24/19 4:46 AM, Raghavendra Rao J S V wrote: Hi All, We are using Postgresql 9.2 database. In one of the transactional table, I have observed duplicate values for the primary key columns. Best guess a corrupted index. As Hellmuth said more information would be helpful. Please guide

Re: Casting Integer to Boolean in assignment

On 1/24/19 7:21 AM, Geoff Winkless wrote: On Thu, 24 Jan 2019 at 15:11, Tom Lane wrote: People don't generally post to the lists after a type-mismatch error catches a typo for them. So it's pretty hard to tell about "how many" developers would find one behavior more useful than the other. It i

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:11, Tom Lane wrote: > People don't generally post to the lists after a type-mismatch error > catches a typo for them. So it's pretty hard to tell about "how > many" developers would find one behavior more useful than the other. > It is safe to say, though, that the same

Re: Casting Integer to Boolean in assignment

On 1/24/19 3:04 AM, Alexandre GRAIL wrote: Hello the list, Maybe this question has been debated before (I didn't find anything helpful) but : Why the default is to throw an error when casting Integer to Boolean in assignment, and accepting it everywhere else ? The overall reason: https://

Re: log_min_duration_statement

On 1/24/19 6:23 AM, bhargav kamineni wrote: Hi Team,, I have set log_min_duration_statement=1000 as my configuration but when i observe log file i could see queries that ran below 1000ms are also getting logged , for instance duration: 0.089 ms  duration: 0.175 ms  duration: 0.139 ms  dura

Re: [pgbackrest] Expiring the last backup?

Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 1/24/19 8:11 AM, Stephen Frost wrote: > >* Ron (ronljohnso...@gmail.com) wrote: > >>On 1/24/19 7:26 AM, Stephen Frost wrote: > >>>* Ron (ronljohnso...@gmail.com) wrote: > The backups partition is running out of disk space, and I need to de

Re: Casting Integer to Boolean in assignment

Geoff Winkless writes: > On Thu, 24 Jan 2019 at 14:28, David G. Johnston > wrote: >> To assist developers in avoiding the writing of buggy queries. > Amazing how many of these developers find this a hindrance. If only > they could see how helpful we're being to them. People don't generally post

Re: Casting Integer to Boolean in assignment

On 1/24/19 9:05 AM, Geoff Winkless wrote: On Thu, 24 Jan 2019 at 14:28, David G. Johnston wrote: To assist developers in avoiding the writing of buggy queries. Amazing how many of these developers find this a hindrance. If only they could see how helpful we're being to them. It's the C vs. A

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 8:11 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: On 1/24/19 7:26 AM, Stephen Frost wrote: * Ron (ronljohnso...@gmail.com) wrote: The backups partition is running out of disk space, and I need to delete the only backup. $ pgbackrest expire --stanza=lo

Re: Postgres 11 chooses seq scan instead of index-only scan

On Thu, Jan 24, 2019 at 9:01 AM twoflower wrote: > Yes! That was it, after running VACUUM TABLE history_translation, the > query is now executed using index-only scan. > > I was under the impression that ANALYZE TABLE history_translation is > enough, but it is not. > Only a VACUUM will update th

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 14:28, David G. Johnston wrote: > To assist developers in avoiding the writing of buggy queries. Amazing how many of these developers find this a hindrance. If only they could see how helpful we're being to them. Geoff

Re: Postgres 11 chooses seq scan instead of index-only scan

Yes! That was it, after running VACUUM TABLE history_translation, the query is now executed using index-only scan. I was under the impression that ANALYZE TABLE history_translation is enough, but it is not. Thank you very much. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general

Re: Postgres 11 chooses seq scan instead of index-only scan

twoflower writes: > One issue I cannot resolve is the new server using a parallel seq scan > instead of index-only scan for the following query: > select count(id) from history_translation You might need to vacuum the table to ensure that the planner thinks a reasonable proportion of the pages ar

Re: How duplicate values inserted into the primary key column of table and how to fix it

Hi you could provide the definition of the table that includes the definition of the primary key? El jue., 24 de ene. de 2019 a la(s) 09:18, Raghavendra Rao J S V ( raghavendra...@gmail.com) escribió: > Hi All, > > > We are using Postgresql 9.2 database. > > > > In one of the transactional table,

Re: Casting Integer to Boolean in assignment

On Thu, Jan 24, 2019 at 4:04 AM Alexandre GRAIL wrote: > But you *cannot* use 1 or 0 as valid input for boolean type when > inserting or updating : > > test=# CREATE TABLE test1 (a boolean); > CREATE TABLE > test=# INSERT INTO test1 VALUES (1); > ERROR: column "a" is of type boolean but expressio

log_min_duration_statement

Hi Team,, I have set log_min_duration_statement=1000 as my configuration but when i observe log file i could see queries that ran below 1000ms are also getting logged , for instance duration: 0.089 ms duration: 0.175 ms duration: 0.139 ms duration: 0.451 ms duration: 0.136 ms duration: 0.340 m

Postgres 11 chooses seq scan instead of index-only scan

I restored a dump of our production DB (running on 9.6) to a Postgres 11 server and wanted to run some basic benchmarks to see if there isn't some unexpected performance drop. One issue I cannot resolve is the new server using a parallel seq scan instead of index-only scan for the following query:

How duplicate values inserted into the primary key column of table and how to fix it

Hi All, We are using Postgresql 9.2 database. In one of the transactional table, I have observed duplicate values for the primary key columns. Please guide me how is it possible and how to fix this kind of issue. -- Regards, Raghavendra Rao J S V

Re: Casting Integer to Boolean in assignment

On 1/24/19 5:04 AM, Alexandre GRAIL wrote: Hello the list, Maybe this question has been debated before (I didn't find anything helpful) but : Why the default is to throw an error when casting Integer to Boolean in assignment, and accepting it everywhere else ? So you can type : postgres

Re: [pgbackrest] Expiring the last backup?

Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 1/24/19 7:26 AM, Stephen Frost wrote: > >* Ron (ronljohnso...@gmail.com) wrote: > >>The backups partition is running out of disk space, and I need to delete the > >>only backup. > >> > >>$ pgbackrest expire --stanza=localhost --retention-full=

Re: [pgbackrest] Expiring the last backup?

On 1/24/19 7:26 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: The backups partition is running out of disk space, and I need to delete the only backup. $ pgbackrest expire --stanza=localhost --retention-full=0 ERROR: [032]: '0' is out of range for 'repo1-retention-

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 12:17, Thomas Kellerer wrote: > Geoff Winkless schrieb am 24.01.2019 um 12:45: > > My own opinion is that non-0 should implicitly cast as true and 0 > > should cast as false. > > I strongly disagree - that would mimic MySQL's idiosyncrasies and would make > such a query val

Re: [pgbackrest] Expiring the last backup?

Greetings, * Ron (ronljohnso...@gmail.com) wrote: > The backups partition is running out of disk space, and I need to delete the > only backup. > > $ pgbackrest expire --stanza=localhost --retention-full=0 > ERROR: [032]: '0' is out of range for 'repo1-retention-full' option Yeah, pgbackrest doe

Re: Need a command to take the backup of the child tables along with its master table.

On Thu, Jan 24, 2019 at 11:01 AM Ron wrote: > If not, you'll have to do some bash magic in parsing the "Foreign-key > constraints:" clause of "\d your_table_name" and stuff them in the "pg_dump > --table=" clause. That would be hard. There is an option to make psql show the queries it uses to i

Re: Casting Integer to Boolean in assignment

Geoff Winkless schrieb am 24.01.2019 um 12:45: > The reason for that at least is that '1' and '0' are valid boolean values. > > https://www.postgresql.org/docs/9.5/datatype-boolean.html > > There's additional text describing why casts are chosen to be defined > as implicit or not here > > https:

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 11:04, Alexandre GRAIL wrote: > > And added to this weirdness is the fact that '1' or '0' (with quote) is OK. > The reason for that at least is that '1' and '0' are valid boolean values. https://www.postgresql.org/docs/9.5/datatype-boolean.html There's additional text desc

Casting Integer to Boolean in assignment

Hello the list, Maybe this question has been debated before (I didn't find anything helpful) but : Why the default is to throw an error when casting Integer to Boolean in assignment, and accepting it everywhere else ? So you can type : postgres=# select 1::boolean;  bool --  t (1 row)

[pgbackrest] Expiring the last backup?

Hi, The backups partition is running out of disk space, and I need to delete the only backup. $ pgbackrest expire --stanza=localhost --retention-full=0 ERROR: [032]: '0' is out of range for 'repo1-retention-full' option So, in version 2.07, what's the secret sauce for deleting that final copy

Re: Get attributes names

Hi Arthur, Thanks for your help. But am using postgres 9.6 version. On Thu, 24 Jan 2019, 15:24 Arthur Zakirov On 24.01.2019 11:54, ramsiddu007 wrote: > > Dear all, > >I hope you are all doing well. Today i got one > > requirement, for that i need attribute names

Re: Need a command to take the backup of the child tables along with its master table.

On 1/24/19 3:40 AM, Raghavendra Rao J S V wrote: Hi All, We have one master table and multiple child tables (dependent tables)  associated with it. While taking the backup of the master table , I would like to take the backup of all its child (dependent) tables backup also. Please guide me

Re: Get attributes names

On 24.01.2019 11:54, ramsiddu007 wrote: Dear all,               I hope you are all doing well. Today i got one requirement, for that i need attribute names as below example. Xml: In the above sample input xml, i want attributes list of emp_deails like. Attributes ___ emp_id emp_na

Need a command to take the backup of the child tables along with its master table.

Hi All, We have one master table and multiple child tables (dependent tables) associated with it. While taking the backup of the master table , I would like to take the backup of all its child (dependent) tables backup also. Please guide me how to take the backup of the master table and its

Get attributes names

Dear all, I hope you are all doing well. Today i got one requirement, for that i need attribute names as below example. Xml: In the above sample input xml, i want attributes list of emp_deails like. Attributes ___ emp_id emp_name dept Thanking you,