Re: [GENERAL] pg_restore out of memory

A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu: > > A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu: > > > > I wrote: > > > > > > I'm still suspicious that this might be some sort of NOTICE- > > > processing- > > > related buffer bloat.  Could you try loading the data with the > >

[GENERAL] Slow query on primary server runs fast on hot standby

Hello, I have a primary PostgreSQL server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 16GB of RAM. My problem is as follows: I've detected a query that takes a long time to run on my primary server but runs very fast on the standby server. I did an

Re: [GENERAL] FTS with more than one language in body and with unknown query language?

Hello, Stefan! On 15.07.2016 01:54, Stefan Keller wrote: приве́т! Artur Thanks for your explanations. 2016-07-14 17:20 GMT+02:00 Artur Zakirov : On 14.07.2016 01:16, Stefan Keller wrote: ... * Should I create a synonym dictionary which contains word translations en-de instead of synonyms en

Re: [GENERAL] Slow query on primary server runs fast on hot standby

On Fri, Jul 15, 2016 at 4:17 PM Kaixi Luo wrote: > Hello, > > I have a primary PostgreSQL server with 64GB of RAM that is replicated > using streaming replication to a hot standby server with 16GB of RAM. > Do you have different hardware configuration for master and standby? I am not sure if tha

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

Hi guys, I'm setting up a new slave server, using Postgres 9.2. This new slave server I'll call: New_slave. I ran this command, from the new_slave server. It will connects to my other slave and copy the DB. ssh postgres@slave05 'pg_basebackup --pgdata=- --format=tar > --label=new_slave --progres

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

sorry... wrong email. Will create a new topic.

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

Hi guys,* (I'm sending this email here because it has the same subject, wal_files)* I'm setting up a new slave server, using Postgres 9.2. This new slave server I'll call: New_slave. I ran this command, from the new_slave server. It will connects to my other slave and copy the DB. ssh postgres@s

Re: [GENERAL] Slow query on primary server runs fast on hot standby

> Do you have different hardware configuration for master and standby? Unfortunately, I do. Changing this is beyond my control at the moment. Also, I made a mistake in my first email. The standby server has 32GB of RAM. Here are the specs: *PRIMARY SERVER* CPU: Intel Xeon E5-1650 v2 @ 3.50GHz RAM

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > I see (transcribed by hand from screenshot): > ... > pg_restore: processing data for table "inspection.positioned_scan" > out of memory > Process returned exit code 1. Right, so that confirms that the OOM happens while sending data for that table; but we're still no closer

[GENERAL] migrating data from an old postgres version

Hi, A coworker is getting a new laptop and he wants to migrate some data from his old one to the new one. So he installed postgres 9.5 on the new one and is asking me how to migrate the data from the old 8.4 database. This database includes postgis. I asked him to make a network connection so tha

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from > his old one to the new one. So he installed postgres 9.5 on the new one and > is asking me how to migrate the data from the old 8.4 database. This > databas

Re: [GENERAL] migrating data from an old postgres version

> On Jul 15, 2016, at 6:55 AM, Melvin Davidson wrote: > > > > On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos > wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from his > old one to the new one. So he installed postgres 9.5 on

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson wrote: > > Why can't he just do a > pg_dump -F p his_dbname > his_dbname.sql > Then copy dbname.sql to a jump/thumb drive on the old laptop > copy the data from the jump/thumb drive to the new laptop > create the new db in 9.5 > and use pg_restore

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 10:02 AM, Paul Ramsey wrote: > > Make sure to pg_dump -Fc > (note the flags) > > ​You need to also include "​--quote-all-identifiers" if you intend for the dump to be restored onto a newer version of PostgreSQL. https://www.postgresql.org/docs/9.6/static/app-pgdump.html

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:02 PM, Paul Ramsey wrote: > Make sure to pg_dump -Fc > (note the flags) > so that in case you need to do a hard restore (if you’re going from > postgis 1.x to 2.x) you can run the dump file through the special cleaning > script. > > See postgis documentation notes on “ha

Re: [GENERAL] Merging timeseries in postgres

Thanks for the replies guys. In the end I found an example lurking in the depths of the Internet that used a CTE and a join to that which seemed to have the desired effect On Thursday, 14 July 2016, Begin Daniel wrote: > *From:* pgsql-general-ow...@postgresql.org > > [mailto:pgsql-general-ow..

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 10:07 AM, Willy-Bas Loos wrote: > > On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson > wrote: > >> >> Why can't he just do a >> pg_dump -F p his_dbname > his_dbname.sql >> Then copy dbname.sql to a jump/thumb drive on the old laptop >> copy the data from the jump/thumb dr

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > ​You need to also include "​--quote-all-identifiers" if you intend for the > dump to be restored onto a newer version of PostgreSQL. > > https://www.postgresql.org/docs/9.6/static/app-pgdump.html > > Hey that

Re: [GENERAL] pg_restore out of memory

On 07/15/2016 12:37 AM, Miguel Ramos wrote: A Qui, 14-07-2016 às 10:52 +0100, Miguel Ramos escreveu: A Qua, 13-07-2016 às 18:42 -0400, Tom Lane escreveu: I wrote: I'm still suspicious that this might be some sort of NOTICE- processing- related buffer bloat. Could you try loading the data

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver wrote: > On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: > >> >> On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson > > wrote: >> >> >> Why can't he just do a >> pg_dump -F p his_dbname > his_dbname.sql >> Then c

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:13 PM, Melvin Davidson wrote: > If you dump with -F p (plain is default) then the version does not matter. > > That is the recommended method for upgrading from older versions. > https://www.postgresql.org/docs/9.4/static/upgrading.html > > Problem is that we use postgis

Re: [GENERAL] migrating data from an old postgres version

On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: Why can't he just do a pg_dump -F p his_dbname > his_dbname.sql Then copy dbname.sql to a jump/thumb drive on the old laptop copy the data from th

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos wrote: > On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver > wrote: > >> >> pg_dump is backwards compatible to version 7.0. >> > > Yes but is it forward compatible from 7.0? > I mean can i restore a dump made with pg_dump 7.0 on a 9.4 database? > > -

Re: [GENERAL] migrating data from an old postgres version

On 07/15/2016 07:24 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: pg_dump is backwards compatible to version 7.0. Yes but is it forward compatible from 7.0? Maybe. I have taken dumps from and old PG version that was cl

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver wrote: > > pg_dump is backwards compatible to version 7.0. > Yes but is it forward compatible from 7.0? I mean can i restore a dump made with pg_dump 7.0 on a 9.4 database? -- Willy-Bas Loos

Re: [GENERAL] migrating data from an old postgres version

On 07/15/2016 07:22 AM, Melvin Davidson wrote: On Fri, Jul 15, 2016 at 10:19 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 07/15/2016 07:07 AM, Willy-Bas Loos wrote: On Fri, Jul 15, 2016 at 3:55 PM, Melvin Davidson mailto:melvin6...@gmail.com>

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:22 PM, Melvin Davidson wrote: > > As Adrian and I have said, pg_dump and pg_dumpall as backwards compatible > and the recommended method for porrting data from one version > to another. You will NOT get errors if you use plain format. > > I can't use plain. And i've had

Re: [GENERAL] migrating data from an old postgres version

On 07/15/2016 07:26 AM, Melvin Davidson wrote: On Fri, Jul 15, 2016 at 10:24 AM, Willy-Bas Loos mailto:willy...@gmail.com>> wrote: On Fri, Jul 15, 2016 at 4:19 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: pg_dump is backwards compatible to version 7.0. Yes

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:31 PM, Adrian Klaver wrote: > Remember you can reconstitute a plain format dump from a custom format > dump by doing: > > pg_restore ... -f plain_sql.txt custom_dump.out > > Not sure what errors you are talking about? > > Nice one! errors in my previous answer directed a

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 10:34 AM, Willy-Bas Loos wrote: > > > On Fri, Jul 15, 2016 at 4:31 PM, Adrian Klaver > wrote: > >> Remember you can reconstitute a plain format dump from a custom format >> dump by doing: >> >> pg_restore ... -f plain_sql.txt custom_dump.out >> >> Not sure what errors you

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 4:40 PM, Melvin Davidson wrote: > Look, if you dump in plain SQL, it is standard to both old and new > versions of PostgreSQL. That is the recommended method to upgrade. > Yes, errors like "row_security" can/will happen, but you can edit and > comment them oout or just ign

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

On 07/15/2016 04:16 AM, Patrick B wrote: Hi guys,/*(I'm sending this email here because it has the same subject, wal_files)*/ I'm setting up a new slave server, using Postgres 9.2. This new slave server I'll call: New_slave. I ran this command, from the new_slave server. It will connects to my

Re: [GENERAL] migrating data from an old postgres version

Willy-Bas Loos writes: > (the manual says:"It is recommended that you use the pg_dump and pg_dumpall > programs from the newer version of PostgreSQL") The reason for the manual's recommendation is that the newer version might contain bug fixes not present in the older one. But discounting that r

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 5:07 PM, Tom Lane wrote: > Willy-Bas Loos writes: > > (the manual says:"It is recommended that you use the pg_dump and > pg_dumpall > > programs from the newer version of PostgreSQL") > > The reason for the manual's recommendation is that the newer version might > contain

[GENERAL] Recovering data from an old disk image

Greetings, I need to recover some data from a disk image that was attached to a virtual machine that has since been deleted. I'm 99.9% sure that postgres was stopped normally before this image was saved. Everything was supposed to have been migrated as part of an upgrade, but it seems that a

Re: [GENERAL] Recovering data from an old disk image

On 07/15/2016 08:37 AM, Richard Kuhns wrote: Greetings, I need to recover some data from a disk image that was attached to a virtual machine that has since been deleted. I'm 99.9% sure that postgres was stopped normally before this image was saved. Everything was supposed to have been migrated

Re: [GENERAL] Recovering data from an old disk image

On 7/15/16 11:37 AM, Richard Kuhns wrote: > I've copied the entire pgsql directory to a new machine & installed the > most recent 9.3 to try to read it. When I start the server it tells me > that the database was initialized by version 9.4, so it can't handle it. > > I uninstalled 9.3 & installe

Re: [GENERAL] Recovering data from an old disk image

Richard Kuhns writes: > I uninstalled 9.3 & installed the most recent 9.4. When I try to start > it, it tells me: > postgres[99770]: [1-1] FATAL: database files are incompatible with server > postgres[99770]: [1-2] DETAIL: The database cluster was initialized > with PG_CONTROL_VERSION 937, bu

Re: [GENERAL] Recovering data from an old disk image

On 07/15/2016 09:06 AM, Tom Lane wrote: Richard Kuhns writes: I uninstalled 9.3 & installed the most recent 9.4. When I try to start it, it tells me: postgres[99770]: [1-1] FATAL: database files are incompatible with server postgres[99770]: [1-2] DETAIL: The database cluster was initialize

Re: [GENERAL] pg_restore out of memory

A Sex, 15-07-2016 às 07:16 -0700, Adrian Klaver escreveu: > On 07/15/2016 12:37 AM, Miguel Ramos wrote: > > What else? > > The pg_dump file you are restoring from is a custom format. > > Do you have room to do something like?: > > 1) pg_restore -d some_db -U some_user -t inspection.positioned_s

Re: [GENERAL] Recovering data from an old disk image

On 07/15/16 12:13, Adrian Klaver wrote: On 07/15/2016 09:06 AM, Tom Lane wrote: Richard Kuhns writes: I uninstalled 9.3 & installed the most recent 9.4. When I try to start it, it tells me: postgres[99770]: [1-1] FATAL: database files are incompatible with server postgres[99770]: [1-2] DET

Re: [GENERAL] FTS with more than one language in body and with unknown query language?

Artur 2016-07-15 11:02 GMT+02:00 Artur Zakirov : ... > This solution does not take into account the fact that queries in german can > be in various forms (for example, plural forms). It works well for english. > As a solution of this, you can put into geo.ths various forms of word. > > But maybe t