Re: Logging [RESOLVED]

On 2019-12-04 16:03:24 -0800, Rich Shepard wrote: > On Wed, 4 Dec 2019, Stephen Eilert wrote: > > Usually, this is done by logrotate or a similar mechanism in your system. > > You’ll likely find that other logs in your system follow a similar > > pattern, not just Postgresql. > > I just checked

Re: migration from 9.4 to 9.6

On Fri, 2019-12-06 at 21:38 +, Julie Nishimura wrote: > I'd like to copy one single database from 9.4 cluster to a new 9.6 > cluster (migration with the upgrade), to the different host > > > > > > > > > > Put 9.4 on the new server. Replicate the db to it. When you're ready to

Re: migration from 9.4 to 9.6

On 12/6/19 1:38 PM, Julie Nishimura wrote: I'd like to copy one single database from 9.4 cluster to a new 9.6 cluster (migration with the upgrade), to the different host Choices: 1) Dump/restore. 2) Afore mentioned Slony. 3) The pglogical

Re: migration from 9.4 to 9.6

I'd like to copy one single database from 9.4 cluster to a new 9.6 cluster (migration with the upgrade), to the different host From: Adrian Klaver Sent: Friday, December 6, 2019 1:32 PM To: Julie Nishimura ; pgsql-general@lists.postgresql.org ; pgsql-general

Re: migration from 9.4 to 9.6

On 12/6/19 1:19 PM, Julie Nishimura wrote: Hello there, what would be my best way to migrate 2 tb single database from cluster 9.4 to another machine on 9.6? Not clear if you want to add database to an existing 9.6 cluster or create a new 9.6 cluster containing the 9.4 database? Thank

migration from 9.4 to 9.6

Hello there, what would be my best way to migrate 2 tb single database from cluster 9.4 to another machine on 9.6? Thank you!

Re: upgrade and migrate

These are great points. Thanks! I will push for some testing environment From: Justin Sent: Friday, December 6, 2019 10:06 AM To: Julie Nishimura Cc: Tom Lane ; Stephen Frost ; Laurenz Albe ; Michael Paquier ; pgsql-general@lists.postgresql.org ;

Re: Slow planing...

pá 6. 12. 2019 v 15:12 odesílatel Mladen Marinović < mladen.marino...@kset.org> napsal: > After a couple of hours of trying different stuff, set enable_mergejoin = > off made the planning time look better: Planning time: 0.322 ms > Any ideas why this helps? > pls, can you try reindex all

Re: upgrade and migrate

Off topic but food for thought given the jump in versions 8.3 to (9.6 or greater.) List of major changes i can think of to watch out for that can bite without warning.. SQL operators~=~ for Like were drop in 8.4 => for hstore was drop in 9.0 in 9.1 standard_conforming_string is ON by default

Re: Q: cert authentication and user remapping fails

Albrecht =?iso-8859-1?b?RHJl3w==?= writes: > In my installation, the user certificate CN's contain human-readable names > (utf8, with spaces, etc.). I want *all* users connecting with cert > authentication to be mapped to a certain database role. I don't think that the user name mapping

Q: cert authentication and user remapping fails

Hi all, I have a problem with psql cert authentication and user mapping. In my installation, the user certificate CN's contain human-readable names (utf8, with spaces, etc.). I want *all* users connecting with cert authentication to be mapped to a certain database role. The server runs on

Re: upgrade and migrate

On 12/6/19 8:14 AM, Julie Nishimura wrote: Thank you everybody for your suggestions. So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix app related issues (if any), then migrate to a version more recent than 9.6, either through pg_basebackup or through logical replication

Re: upgrade and migrate

Thank you everybody for your suggestions. So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix app related issues (if any), then migrate to a version more recent than 9.6, either through pg_basebackup or through logical replication (if we would upgrade to version 10). First

Re: Slow planing...

On Fri, Dec 6, 2019, 3:21 AM Mladen Marinović wrote: > Is there a way to detect why the planing is taking this long? > > The database is a 9.6.1 with 32GB of shared_buffers, and 1GB of > maintanance_work_mem, and machine CPU is below 80% all the time. > What is default_statistics_target set to

Re: Insert Table from Execute String Query

On 12/6/19 1:00 AM, İlyas Derse wrote: I need to insert temp table from execute string query. How can I do ? I'm trying like that but not working. CREATE OR REPLACE FUNCTION public.testdyn (         x integer ) RETURNS TABLE (         id bigint,         text character varying(4000) ) AS $$

Re: Slow planing...

=?UTF-8?Q?Mladen_Marinovi=C4=87?= writes: > After a couple of hours of trying different stuff, set enable_mergejoin = > off made the planning time look better: Planning time: 0.322 ms > Any ideas why this helps? Hmm, possibly because it's reducing the number of calls to

Re: Slow planing...

After a couple of hours of trying different stuff, set enable_mergejoin = off made the planning time look better: Planning time: 0.322 ms Any ideas why this helps? Regards, Mladen Marinović On Fri, Dec 6, 2019 at 11:14 AM Mladen Marinović wrote: > Hi, > > Since this morning our system is

Re: archiving question

On Fri, Dec 6, 2019 at 12:06 PM Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote: > > On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ) markus.zwett...@zuerich.ch> wrote: > >> -Ursprüngliche Nachricht- > >> Von: Michael Paquier > >> Gesendet:

Re: [11.6] WALs recovery ordering with Restore Command - timelines

One more log example: > > 2019-12-06 07:11:16 CST LOG: database system was shut down in recovery > at 2019-12-06 07:11:08 CST > Searching WAL: 0022.history, location: pg_wal/RECOVERYHISTORY > 2019-12-06 07:11:16 CST LOG: restored log file "0022.history" from > archive > Searching WAL:

AW: archiving question

> On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ) > wrote: >> -Ursprüngliche Nachricht- >> Von: Michael Paquier >> Gesendet: Freitag, 6. Dezember 2019 02:43 >> An: Zwettler Markus (OIZ)

Slow planing...

Hi, Since this morning our system is running slower than usual. It turns out that some queries take a very long time to plan ( > 1 second). The problem occurs when joining bigger tables. There are no partition for the used tables. The problem has a time correlation with the last

Re: archiving question

On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote: > > -Ursprüngliche Nachricht- > > Von: Michael Paquier > > Gesendet: Freitag, 6. Dezember 2019 02:43 > > An: Zwettler Markus (OIZ) > > Cc: Stephen Frost ; > pgsql-general@lists.postgresql.org > >

AW: archiving question

> -Ursprüngliche Nachricht- > Von: Michael Paquier > Gesendet: Freitag, 6. Dezember 2019 02:43 > An: Zwettler Markus (OIZ) > Cc: Stephen Frost ; pgsql-general@lists.postgresql.org > Betreff: Re: archiving question > > On Thu, Dec 05, 2019 at 03:04:55PM +, Zwettler Markus (OIZ)

Tuple concurrency issue in large objects

Hi all, I am working on a project which allows multiple users to work on single large text document. I am using lo_put to apply only the diff into the large object without replacing it with a new lob. While working on it, I encountered an error "Tuple concurrently updated". The error can be

Insert Table from Execute String Query

I need to insert temp table from execute string query. How can I do ? I'm trying like that but not working. CREATE OR REPLACE FUNCTION public.testdyn ( x integer ) RETURNS TABLE ( id bigint, text character varying(4000) ) AS $$ DECLARE mysql TEXT; BEGIN