Re: [GENERAL] Detailed progress reporting for "vacuuming indexes" stage

2017-01-23 Thread Michael Paquier
On Mon, Jan 23, 2017 at 8:32 PM, Alexander Shchapov wrote: > There are 7 stages for standard VACUUM process which is reportable via > this view. For time consuming stages like "scanning heap" or > "vacuuming heap" you are able to get stage progress idea by doing: > > (heap_blks_scanned | heap_blks

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Tom Lane
Adrian Klaver writes: > On 01/23/2017 05:14 PM, David G. Johnston wrote: >> To your example - testing in UTC is going to always result in failure >> for Z values <= 0 since they will all result in a UTC date of >> 2011-01-01. Choosing +06 would result in a passed test. > That was sort of the poi

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Adrian Klaver
On 01/23/2017 05:14 PM, David G. Johnston wrote: On Mon, Jan 23, 2017 at 6:08 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: production=# show timezone; TimeZone -- UTC (1 row) production=# select ' 2011-01-01 00:00:03.925-06'::timestamptz < '

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread David G. Johnston
On Mon, Jan 23, 2017 at 6:08 PM, Adrian Klaver wrote: > > production=# show timezone; > TimeZone > -- > UTC > (1 row) > > production=# select ' 2011-01-01 00:00:03.925-06'::timestamptz < > '2011-01-01'::date; > ?column? > -- > f > ​What Tom said - though I missed the part ab

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Adrian Klaver
On 01/23/2017 05:01 PM, David G. Johnston wrote: On Mon, Jan 23, 2017 at 5:57 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: * this the constraint: "time_stamp_201012ad" CHECK (time_stamp >= '2010-12-01'::date AND time_stamp < '2011-01-01'::

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Tom Lane
Edmundo Robles writes: > * this the constraint: "time_stamp_201012ad" CHECK (time_stamp >= > '2010-12-01'::date AND time_stamp < '2011-01-01'::date) If that's on a timestamptz column, it's a seriously dangerous constraint, because the limitations it enforces are dependent on the current timezon

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread David G. Johnston
On Mon, Jan 23, 2017 at 5:57 PM, Adrian Klaver wrote: > > * this the constraint: "time_stamp_201012ad" CHECK (time_stamp >= >> '2010-12-01'::date AND time_stamp < '2011-01-01'::date) >> >> if change 2011-01-01 00:00:03.925+00 to 2011-01-01 >> 00:00:03.925-06 wor

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Adrian Klaver
On 01/23/2017 04:30 PM, Edmundo Robles wrote: * at last, i did 'pg_restore -l backup.dmp| sed "s:+00:-06:g"|psql The above is not the complete command, correct? the complete command is: pg_restore backup.dmp| sed "s:+00:-06:g"|psql database Another thought, do you ha

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread David G. Johnston
On Mon, Jan 23, 2017 at 4:45 PM, Edmundo Robles wrote: > * This is the message: COPY failed for table "stlm_201012ad": ERROR: new > row for relation "stlm_201012ad" violates check constraint > "time_stamp_201012ad" > DETAIL: Failing row contains (..., 2011-01-01 00:00:03.925+00, .., 0). > > *

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Adrian Klaver
On 01/23/2017 04:30 PM, Edmundo Robles wrote: On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 01/23/2017 03:45 PM, Edmundo Robles wrote: Hi! I have backed up a database with a 'UTC' timezone, but i can not to

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Edmundo Robles
On Mon, Jan 23, 2017 at 6:02 PM, Adrian Klaver wrote: > On 01/23/2017 03:45 PM, Edmundo Robles wrote: > >> Hi! >> >> I have backed up a database with a 'UTC' timezone, but i can not to >> restore it on a database with 'localtime' timezone. >> > > Dealing with time is complex, so some inline q

Re: [GENERAL] pg_dump Conflict with recovery

2017-01-23 Thread julyanto SUTANDANG
On Tue, Jan 24, 2017 at 5:45 AM, Israel Brewster wrote: > On Jan 23, 2017, at 10:06 AM, Jerry Sievers > wrote: > > > Israel Brewster writes: > > I have a backup strategy that in part consists of doing pg_dump s on my > various databases. In order to hopefully reduce/prevent operational > slow-d

Re: [GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Adrian Klaver
On 01/23/2017 03:45 PM, Edmundo Robles wrote: Hi! I have backed up a database with a 'UTC' timezone, but i can not to restore it on a database with 'localtime' timezone. Dealing with time is complex, so some inline questions below to try to figure out exactly what the setup is. I hav

Re: [GENERAL] What is the purpose of PostGIS on PostgreSQL?

2017-01-23 Thread Merlin Moncure
On Mon, Jan 23, 2017 at 11:08 AM, Kased, Razy (Chemistry and Geosciences) wrote: > I recently came across this question: "What is the purpose of PostGIS on > PostgreSQL?" and wanted to know what this mailing list had to respond with. PostGIS is a SQL wrapper to the GEOS library which lets you do

[GENERAL] recovery dump on database with different timezone

2017-01-23 Thread Edmundo Robles
Hi! I have backed up a database with a 'UTC' timezone, but i can not to restore it on a database with 'localtime' timezone. I have a time constraints, the reason of the mistakes. * First, I did: 'alter database set timezone='UTC'; 'but no restore it... * After I did change 'timezone

Re: [GENERAL] pg_dump Conflict with recovery

2017-01-23 Thread Israel Brewster
On Jan 23, 2017, at 10:06 AM, Jerry Sievers wrote: > > Israel Brewster writes: > >> I have a backup strategy that in part consists of doing pg_dump s on my >> various databases. In order to hopefully reduce/prevent operational >> slow-down as a result of the >> backup, I do the dumps from my

[GENERAL] Why is materialized view creation a "security-restricted operation"?

2017-01-23 Thread Joshua Chamberlain
Hello, I see this has been discussed briefly before[1], but I'm still not clear on what's happening and why. I wrote a function that uses temporary tables in generating a result set. I can use it when creating tables or views, e.g., CREATE TABLE some_table AS SELECT * FROM my_func(); CREATE VIEW

Re: [GENERAL] pg_dump Conflict with recovery

2017-01-23 Thread Jerry Sievers
Israel Brewster writes: > I have a backup strategy that in part consists of doing pg_dump s on my > various databases. In order to hopefully reduce/prevent operational slow-down > as a result of the > backup, I do the dumps from my secondary server, configured as a hot standby > with streaming

Re: [GENERAL] Why does this hot standy archive_command work

2017-01-23 Thread Jerry Sievers
"bto...@computer.org" writes: > - Original Message - >> From: "Jerry Sievers" >> To: "Steve Atkins" >> Cc: "pgsql-general" >> Sent: Monday, January 23, 2017 12:52:46 PM >> Subject: Re: [GENERAL] Why does this hot standy archive_command work >> >> Steve Atkins writes: >> >> >> On Jan

[GENERAL] pg_dump Conflict with recovery

2017-01-23 Thread Israel Brewster
I have a backup strategy that in part consists of doing pg_dump s on my various databases. In order to hopefully reduce/prevent operational slow-down as a result of the backup, I do the dumps from my secondary server, configured as a hot standby with streaming replication.In general this works fine

Re: [GENERAL] Why does this hot standy archive_command work

2017-01-23 Thread bto...@computer.org
- Original Message - > From: "Jerry Sievers" > To: "Steve Atkins" > Cc: "pgsql-general" > Sent: Monday, January 23, 2017 12:52:46 PM > Subject: Re: [GENERAL] Why does this hot standy archive_command work > > Steve Atkins writes: > > >> On Jan 20, 2017, at 7:03 PM, bto...@computer.or

Re: [GENERAL] Why does this hot standy archive_command work

2017-01-23 Thread Jerry Sievers
Steve Atkins writes: >> On Jan 20, 2017, at 7:03 PM, bto...@computer.org >> wrote: >> >> While learning a bit about basic hot standby configuration, I was reviewing >> an article that used these parameters >> >> wal_level = 'hot_standby' >> archive_mode = on >> archive_command = 'cd .' That

Re: [GENERAL] What is the purpose of PostGIS on PostgreSQL?

2017-01-23 Thread Adrian Klaver
On 01/23/2017 09:08 AM, Kased, Razy (Chemistry and Geosciences) wrote: I recently came across this question: "What is the purpose of PostGIS on PostgreSQL?" and wanted to know what this mailing list had to respond with. See the paragraph under the h

[GENERAL] What is the purpose of PostGIS on PostgreSQL?

2017-01-23 Thread Kased, Razy (Chemistry and Geosciences)
I recently came across this question: "What is the purpose of PostGIS on PostgreSQL?" and wanted to know what this mailing list had to respond with. ?Thanks,

[GENERAL] FTS prefix search - put full hits first

2017-01-23 Thread cen
Hi I have this FTS query: SELECT * FROM receivers r WHERE r.tsv @@ unaccent('john:*')::tsquery ORDER BY ts_rank(r.tsv, unaccent('john:*')::tsquery) DESC; Is there any way to tell FTS to put records with "John" first and others after that (Johhny, Johnson etc)? Basically, I want to have ful

[GENERAL] Detailed progress reporting for "vacuuming indexes" stage

2017-01-23 Thread Alexander Shchapov
Hi all, We've recently upgraded to 9.6.1 and now playing with newly available pg_stat_progress_vacuum view. There are 7 stages for standard VACUUM process which is reportable via this view. For time consuming stages like "scanning heap" or "vacuuming heap" you are able to get stage progress idea