Re: Resync second slave to new master

If not set, could you add recovery.conf file recovery_target_timeline='latest' parameter? https://www.postgresql.org/docs/devel/static/recovery-target-settings.html 2018-03-08 10:41 GMT+03:00 Dylan Luong : > Hi Michael, > > I tested the failover today and the slave 2

RE: Resync second slave to new master

Hi Michael, I tested the failover today and the slave 2 failed to resync with the new master (old slave1). After I promoted the slave1 to become master, I was able to use pg_rewind on the old master and bring it back as new slave. I then stopped slave2 and ran pg_rewind on slave2 against new

Re: Prefixing schema name

On 08/03/2018 01:13, David G. Johnston wrote: On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang >wrote: ​ The search_path configuration works only for queries. ​Um ​https://www.postgresql.org/docs/10/static/sql-createschema.html

Replication push instead of pull

We have several servers, our masters, installed on several cities. Then we need have a centralized database where we can do our reports. So, postgres publication/subscription model always opens Master server to Slave gets its data.. There is a way to push data from master to slave instead of

Timescale with replication advice

I was wondering if any list members have experience setting up TimeScale with streaming replication. http://www.timescale.com/ I plan to use a collection of 2-5 EC2 instances in AWS and want to set up 1 master -> N slaves to allow read-only connection pooling on

Re: cached plan must not change result type

On Wed, Mar 7, 2018 at 4:06 PM, Jan Bilek wrote: > I would like to ask, would you see this solution in general as fine, or is > there any better way to achieve this? I particularly dislike the part when > we are trying matching return string on "cached plan must not

Re: Prefixing schema name

On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang wrote: > ​ > The search_path configuration works only for queries. > ​Um ​https://www.postgresql.org/docs/10/static/sql-createschema.html "​A CREATE command specifying an unqualified object name creates the object in

cached plan must not change result type

Hi all, Our client noticed a problem which occurred so far twice, but might be having quite significant impact on our application processing in production: ResStatus: PGRES_FATAL_ERROR transaction. ErrorMessage: ERROR: cached plan must not change result type. Reading through the documentation

Prefixing schema name

Hi, Would it be possible to create a table or index in a schema without prefixing the schema name? I find it a hassle to keep specifying the schema name when I create an object or performing DMLs in a schema. The search_path configuration works only for queries. For example: Is there a way to run

Re: Help troubleshooting SubtransControlLock problems

Scott Frazer wrote: > Currently this seems to be happening when the server just starts taking > connections. It doesn't even need time to scale up anymore (previously it > took about 8 hours for the problem to re-appear) > > It's only happening on the read replicas, though. I've just set my

Re: Help troubleshooting SubtransControlLock problems

On Wed, Mar 7, 2018 at 10:39 AM, Alvaro Herrera wrote: > > Laurenz Albe wrote: > I think you could get in this situation if the range of open > transactions exceeds what fits in the buffers for subtrans.c pages, and > the subtransaction cache overflows (64 entries apiece;

Re: Replication pull instead of push

My question was ... how to pull data from Master into Slave. Postgres has a Publication/Subscription model that Slave connects to Master and gets the data. I want something that Master server gets its data which needs to be replicated and send that data to Slave. Is that way possible ? If there

Re: Replication pull instead of push

2018-03-07 16:01 GMT-03:00 PegoraroF10 : > > We have several servers, our masters, installed on several cities. Then we > need have a centralized database (our standby) where we can do our reports. > So, usual way of doing postgres replication is creating a >

Replication pull instead of push

We have several servers, our masters, installed on several cities. Then we need have a centralized database (our standby) where we can do our reports. So, usual way of doing postgres replication is creating a replication/subscription process. But then that master server should be reachable and

Re: a very primitive question about division

In Table 9.5 the division of 9 by 4 is indeed described clearly. But there is no example of 4/9 and the different ways of formatting it as a decimal fraction with different options for rounding or a percentage. Two or three added examples would make life easier for folks who have not

Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:30 AM, Martin Mueller < martinmuel...@northwestern.edu> wrote: > Thanks. So round(before1550/colfreq::numeric, 2) produces the desired > result. > > > > The explanations and examples of string functions in the Postgres > documentation are a model of clarity. The

Re: a very primitive question about division

Thanks. So round(before1550/colfreq::numeric, 2) produces the desired result. The explanations and examples of string functions in the Postgres documentation are a model of clarity. The explanations and examples of basic arithmetic operations are not. There is room for improvement there.

Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:21 AM, Martin Mueller < martinmuel...@northwestern.edu> wrote: > Given two values defined as integers, how do I divide one by the other and > get an answer with two decimals, e.g 3 /4 = 0.75. > ​Case one of them to numeric. ​select 3/4::numeric ​ ​David J.​

Re: Help troubleshooting SubtransControlLock problems

Laurenz Albe wrote: > Scott Frazer wrote: > > Hi, we have a Postgres 9.6 setup using replication that has recently > > started seeing a lot of processes stuck in > > "SubtransControlLock" as a wait_event on the read-replicas. Like this, only > > usually about 300-800 of them: > > > > > >

Re: Help troubleshooting SubtransControlLock problems

Scott Frazer wrote: > > On Wed, Mar 7, 2018 at 9:52 AM, Laurenz Albe wrote: > > Scott Frazer wrote: > > > Hi, we have a Postgres 9.6 setup using replication that has recently > > > started seeing a lot of processes stuck in > > > "SubtransControlLock" as a wait_event

Re: Help troubleshooting SubtransControlLock problems

Server version is 9.6.5 Is there a decent guide to getting a stack trace on Centos7 when using the official Postgres repo? trying to follow the Fedora guide put the debug info for 9.2.23 on the box instead of the 9.6.5 version. On Wed, Mar 7, 2018 at 9:52 AM, Laurenz Albe

Re: Help troubleshooting SubtransControlLock problems

Scott Frazer wrote: > Hi, we have a Postgres 9.6 setup using replication that has recently started > seeing a lot of processes stuck in > "SubtransControlLock" as a wait_event on the read-replicas. Like this, only > usually about 300-800 of them: > > > 179706 | LWLockNamed |

Re: Authentication?

Greetings, * Benedict Holland (benedict.m.holl...@gmail.com) wrote: > Not to get off topic, can you authenticate database users via Kerberos? Absolutely. GSSAPI is the auth method to use for Kerberos. Thanks! Stephen

Re: Authentication?

Not to get off topic, can you authenticate database users via Kerberos? Thanks, ~Ben On Wed, Mar 7, 2018 at 10:19 AM, Stephen Frost wrote: > Greetings, > > * Bjørn T Johansen (b...@havleik.no) wrote: > > Is it possible to use one authentication method as default, like LDAP,

Re: Help troubleshooting SubtransControlLock problems

These don't seem like normal locks. Nothing shows up in a "SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;" These processes are all active but the wait_event and wait_event_type fields indicate they are waiting on (I believe) shared memory locks. pid | usesysid | usename

Re: Authentication?

Greetings, * Bjørn T Johansen (b...@havleik.no) wrote: > Is it possible to use one authentication method as default, like LDAP, and if > the user is not found, then try to authenticate using > md5/scram-sha-256 ? Not directly in pg_hba.conf. You might be able to construct a system which works

Re: Authentication?

On Wed, Mar 7, 2018 at 8:14 AM, Bjørn T Johansen wrote: > On Wed, 7 Mar 2018 07:14:55 -0700 > "David G. Johnston" wrote: > > > On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen wrote: > > > > > Hi. > > > > > > Is it possible to use

Re: Authentication?

On Wed, 7 Mar 2018 07:14:55 -0700 "David G. Johnston" wrote: > On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen wrote: > > > Hi. > > > > Is it possible to use one authentication method as default, like LDAP, and > > if the user is not found, then

Re: Authentication?

On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen wrote: > Hi. > > Is it possible to use one authentication method as default, like LDAP, and > if the user is not found, then try to authenticate using > md5/scram-sha-256 ? > ​In the "Client Authentication" Chapter:​

Re: save query as sql file

thanks you! Jacek 2018-03-07 15:00 GMT+01:00 Melvin Davidson : > > > On Wed, Mar 7, 2018 at 8:46 AM, Łukasz Jarych wrote: > >> thank you ! >> >> Jacek >> >> 2018-03-07 14:45 GMT+01:00 Adrian Klaver : >> >>> On 03/06/2018

Re: save query as sql file

On Wed, Mar 7, 2018 at 8:46 AM, Łukasz Jarych wrote: > thank you ! > > Jacek > > 2018-03-07 14:45 GMT+01:00 Adrian Klaver : > >> On 03/06/2018 11:07 PM, Łukasz Jarych wrote: >> >>> Hello, >>> >>> I ma trying to save query as sql file in pg_admin4

Re: save query as sql file

thank you ! Jacek 2018-03-07 14:45 GMT+01:00 Adrian Klaver : > On 03/06/2018 11:07 PM, Łukasz Jarych wrote: > >> Hello, >> >> I ma trying to save query as sql file in pg_admin4 but file --> save as >> not exists like here: >> >>

Re: save query as sql file

On 03/06/2018 11:07 PM, Łukasz Jarych wrote: Hello, I ma trying to save query as sql file in pg_admin4 but file --> save as not exists like here: https://www.youtube.com/watch?v=L4KJ_Kpymh4 where can i do it ? Well according to manual:

Authentication?

Hi. Is it possible to use one authentication method as default, like LDAP, and if the user is not found, then try to authenticate using md5/scram-sha-256 ? Regards, BTJ -- --- Bjørn T Johansen

Re: help to query json column

Thanks Charles. that worked. > On Mar 7, 2018, at 12:27 PM, Charles Clavadetscher > wrote: > > Hello > >> -Original Message- >> From: Arup Rakshit [mailto:aruprakshit1...@outlook.com] >> Sent: Mittwoch, 7. März 2018 05:41 >> To:

Re: Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "merge".

Vinodh NV wrote: > Sir, > > I am facing the error Caused by: org.postgresql.util.PSQLException: ERROR: > syntax error at or near "merge". when I execute the below query in > postgres database. The same works fine in Oracle. > > merge into net n using dual on (n.id=:id) when matched then update

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "merge".

Sir, I am facing the error Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "merge". when I execute the below query in postgres database. The same works fine in Oracle. merge into net n using dual on (n.id=:id) when matched then update set Status=:status,