Re: [GENERAL] controlled switchover with repmgr

Dylan Luong wrote: > Hi > > > > In a controlled switchover from master to slave with repmgr, what is the > downtime timeframe? Seconds? yes, seconds. > > What is the internal process involved in a switchover with repmgr? Is the > process all automated? Will there be data loss? repmgr fir

Re: [GENERAL] index on search - pg 9.2

On 3/14/2017 7:18 PM, Patrick B wrote: SELECT j.id , ff.gtime FROM public.status AS s JOIN public.job AS j ON j.status_label_id = s.id AND j.clientid = 3369 JOIN public.log AS ff ON ff.jobid = j.id AND ff.clientid = 3369 AND (f

[GENERAL] index on search - pg 9.2

Hi guys I've got a query that is doing a search with wildcards: > OR (description LIKE '%change%') Query: - Taking > 14 secs to run > SELECT j.id, ff.gtime > FROM public.status AS s > JOIN public.job AS j ON j.status_label_id = s.id AND j.clientid = 3369 > JOIN public.log AS ff ON ff.jobid = j

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On Tue, Mar 14, 2017 at 4:55 AM, Alexander Farber wrote: > I wish there would be possibility to add ON CONFLICT DO NOTHING /* and drop > the review */ to the both UPDATE's above, but there is no such thing > described at https://www.postgresql.org/docs/9.5/static/sql-update.html Heikki wanted to

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On Tue, Mar 14, 2017 at 1:25 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > But this might give me conflicts, because there might be such a PK > already... > > You need to remove the "might" and figure out which ones will and which will not. You can update the ones will not and del

[GENERAL] Postgres backup solution

Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or tak

Re: [GENERAL] Postgres backup solution

Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or tak

[GENERAL] controlled switchover with repmgr

Hi In a controlled switchover from master to slave with repmgr, what is the downtime timeframe? Seconds? What is the internal process involved in a switchover with repmgr? Is the process all automated? Will there be data loss? Regards Dylan

Re: [GENERAL] Postgres backup solution

On Tue, 14 Mar 2017, Adrian Klaver wrote: What would be a recommended solution for backing up a very large Postgres (~13TeraBytes) database in order to prevent from data deletion/corruption. Current setup is only to backup/restore to a standby read-only Postgres server via AWS S3 using wal-e how

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 01:25 PM, Alexander Farber wrote: Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only

Re: [GENERAL] Postgres backup solution

On 03/14/2017 01:58 PM, Lawrence Cohan wrote: Cut-and-paste from winmail.dat: "Let's try this one more time in plain text and please note that I'm creating the email totally in plain text, and send as plain text. Unfortunately I have no control on the mail server if that's where this message

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver wrote: > On 03/14/2017 09:00 AM, Alexander Farber wrote: > >> My initial idea has been not to use ON CONFLICT at all, but instead only >> UPDATE the words_reviews records (set "uid" or "author" to out_uid) for >> which NO EXISTS already

Re: [GENERAL] Table not cleaning up drom dead tuples

On Tue, Mar 14, 2017 at 5:09 AM, Антон Тарабрин wrote: > Good day. It seems that we have some strange case of VACUUM malfunction > and table bloating. > > PostgreSQL 9.5.3 > Are you using replication slots? See this, fixed in 9.5.5: commit de396a1cb34626619ddc6fb9dec6d12abee8b589 Author: Andre

Re: [GENERAL] Postgres backup solution

Lawrence, First off, I strongly recommend that you figure out how to send regular plain-text emails, at least to this mailing list, as the whole "winmail.dat" thing is going to throw people off and you're unlikely to get many responses because of it. Regarding your question.. * Lawrence Cohan (l

Re: [GENERAL] Postgres backup solution

Your message is not diplaying. At least not for me. I guess that my reader does not understand the "smime.p7m" file, which shows as an attachment. For others, his question is: === original question from Lawrence Cohan === Yes, this is what I intended to ask: What would be a recommended solution

Re: [GENERAL] Postgres backup solution

Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or tak

Re: [GENERAL] Postgres backup solution

On 3/14/2017 12:31 PM, Lawrence Cohan wrote: Subject: Postgres backup solution From: Lawrence Cohan Date: 3/14/2017 12:31 PM To: "pgsql-general@postgresql.org" was there supposed to be a question or statement or something here ? -- john r pierce, recycling bits in santa cruz

[GENERAL] Postgres backup solution

Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or tak

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then I am afraid the logic is escap

Re: [GENERAL] Maximum of connections in PG

On 03/14/2017 09:08 AM, Durumdara wrote: Dear Melvin! What is the meaning of PgBouncer with persistent, non-interruptable connections? To I know it (for learn). They are non web connections (request, get connection, result, drop connection), they are pure, native applications which are keeping

Re: [GENERAL] Maximum of connections in PG

On Tue, Mar 14, 2017 at 12:08 PM, Durumdara wrote: > Dear Melvin! > > What is the meaning of PgBouncer with persistent, non-interruptable > connections? To I know it (for learn). > > They are non web connections (request, get connection, result, drop > connection), they are pure, native applicati

Re: [GENERAL] Table not cleaning up drom dead tuples

Nope, we've checked everything before deciding to write in mail list. We have no idea why it's happening. вт, 14 мар. 2017 г. в 19:10, Glyn Astill : > And no prepared transactions you say? > > select * from pg_prepared_xacts; > > Perhaps someone else will chime in ... > -- __

Re: [GENERAL] Table not cleaning up drom dead tuples

> We're, in general, pretty carefull with our DB, as it contains important > data. > Most rollback is issued by application (which processes all data inside > transactions). > > p.s. Time is in UTC (GMT+0) > > =# select min(xact_start) from pg_stat_activity where state<>'idle'; >

Re: [GENERAL] Maximum of connections in PG

Dear Melvin! What is the meaning of PgBouncer with persistent, non-interruptable connections? To I know it (for learn). They are non web connections (request, get connection, result, drop connection), they are pure, native applications which are keeping connection from the start to the terminatio

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured

Re: [GENERAL] Table not cleaning up drom dead tuples

We're, in general, pretty carefull with our DB, as it contains important data. Most rollback is issued by application (which processes all data inside transactions). p.s. Time is in UTC (GMT+0) =# select min(xact_start) from pg_stat_activity where state<>'idle'; min ---

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 08:38 AM, Alexander Farber wrote: Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/14/2017 07:23 AM, Alexander Farber wrote: in _uids array I have all user ids of player. I want to merge his or her

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver wrote: > On 03/14/2017 07:23 AM, Alexander Farber wrote: > >> >> in _uids array I have all user ids of player. >> >> I want to merge his or her data, including reviews, to a single user id: >> out_uid. >> >> So I make a copy of related

Re: [GENERAL] Table not cleaning up drom dead tuples

> This tables is original ones, it doesn't have any activity now. We copied > data to NEW tables and trying to solve root of the problem > > - target database where broken tables are located > > > - VACUUM FULL VERBOSE > =# VACUUM (FULL, VERBOSE) __orders_y2017_m2_to_drop; > INFO: vacuu

Re: [GENERAL] Table not cleaning up drom dead tuples

This tables is original ones, it doesn't have any activity now. We copied data to NEW tables and trying to solve root of the problem - target database where broken tables are located - VACUUM FULL VERBOSE =# VACUUM (FULL, VERBOSE) __orders_y2017_m2_to_drop; INFO: vacuuming "public.__orders_

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 07:23 AM, Alexander Farber wrote: Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has Alright I see that you are setting

Re: [GENERAL] Table not cleaning up drom dead tuples

> > From: Антон Тарабрин > To: "pgsql-general@postgresql.org" > Sent: Tuesday, 14 March 2017, 14:05 > Subject: Re: [GENERAL] Table not cleaning up drom dead tuples > > > Yep. VACUUM FULL not helping us on OLD table, that are not getting updated > and not used

Re: [GENERAL] Maximum of connections in PG

On 03/14/2017 07:15 AM, Durumdara wrote: Dear Members! In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"? 1000? 2000? The clients are native applications (Windows executables) with persistent connections, with more tha

Re: [GENERAL] Maximum of connections in PG

On Tue, Mar 14, 2017 at 10:15 AM, Durumdara wrote: > Dear Members! > > In a very strong Linux machine (with many 16-30 GB RAM) what is the limit > of the PGSQL server (9.4-9.5) "maximum connections"? > > 1000? > 2000? > > The clients are native applications (Windows executables) with persistent >

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has been rated or this user has rated someone) and then re-INSERT ON CONFLICT DO NOTHING t

[GENERAL] Maximum of connections in PG

Dear Members! In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"? 1000? 2000? The clients are native applications (Windows executables) with persistent connections, with more than 100 databases (every client have only one

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 06:52 AM, Alexander Farber wrote: I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid,

Re: [GENERAL] Table not cleaning up drom dead tuples

Yep. VACUUM FULL not helping us on OLD table, that are not getting updated and not used in any requests. Bloat is still there This is production system, so now we are investigating why it's happening. > Information about problematic tables: > https://gist.github.com/tarabanton/edf7f8dc26eb7ec37a9c

Re: [GENERAL] Table not cleaning up drom dead tuples

> From: Антон Тарабрин > To: pgsql-general@postgresql.org > Sent: Tuesday, 14 March 2017, 12:09 > Subject: [GENERAL] Table not cleaning up drom dead tuples > General info about our database: > https://gist.github.com/aCLr/dec78ab031749e517550ac11f8233f70 > > Information about problematic t

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Sorry, missed the last DELETE: DELETE FROM words_reviews WHERE author <> out_uid AND author = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid, author, nice,

Re: [GENERAL] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

On Tue, Mar 14, 2017 at 6:40 PM, Rakesh Kumar wrote: >>More to the point, whatever "MorphOS" is, it isn't AmigaOS. It was pretty >>clearly stated in the original thread that AmigaOS had no support for >>fork(), without which there was no chance of running Postgres. > > Just curious, if PG is comp

[GENERAL] Table not cleaning up drom dead tuples

Good day. It seems that we have some strange case of VACUUM malfunction and table bloating. PostgreSQL 9.5.3 OS #uname -a FreeBSD db-host 10.2-RELEASE-p18 FreeBSD 10.2-RELEASE-p18 #0: Sat May 28 08:53:43 UTC 2016 r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC amd64 General info a

[GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Good afternoon, I am working on a small game, where the mobile app initially sends social network user data (name, city, gender) to PostgreSQL 9.5 backend and I store that data in a table: CREATE TABLE words_social ( sid varchar(255) NOT NULL, social integer NOT NULL CHECK (0 <=

Re: [GENERAL] createuser: How to specify a database to connect to

> -Ursprüngliche Nachricht- > Von: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Gesendet: Montag, 13. März 2017 17:28 > An: Tom Lane > Cc: Schmid Andreas; 'pgsql-general@postgresql.org' > Betreff: Re: [GENERAL] createuser: How to specify a database to connect to > > On 03/13/2017 09:

Re: [GENERAL] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

>More to the point, whatever "MorphOS" is, it isn't AmigaOS. It was pretty >clearly stated in the original thread that AmigaOS had no support for >fork(), without which there was no chance of running Postgres. Just curious, if PG is completely dependent on fork(), how was it ported to Windows w

Re: [GENERAL] DELETE and JOIN

Good morning and thank you for the replies. I've ended up with the following DELETE USING (in order to delete reviews coming from different user id, but same IP address in the last 24 hours): DELETE FROM words_reviews r USING words_users u WHERE r.uid = u.uid