[GENERAL] Restoring tables with circular references dumped to separate files

2017-10-20 Thread doganmeh
I was dumping each table to a separate file so I could pick and choose when restoring. However, seems this was not a great idea, since two of my tables happened to reference each other via FOREIGN KEYs, and I am not able to restore them. Is there a way to do this without manually merging the dump

Re: [GENERAL] Weird performance difference

2017-10-20 Thread Tom Lane
Israel Brewster writes: >> Can you send "explain" (not explain analyze) for the production server? > Not for the full query - it only just completed, after 70 minutes or so, and > I wasn't running under EXPLAIN ANALYZE. Running with a shorter date range of > only 7 days,

Re: [GENERAL] Weird performance difference

2017-10-20 Thread Israel Brewster
On Oct 20, 2017, at 3:31 PM, Justin Pryzby wrote: > > On Fri, Oct 20, 2017 at 03:08:26PM -0800, Israel Brewster wrote: >> Summary: the following query takes around 12 seconds on my test machine. On >> my production machine, it's at half an hour and counting. What's going

Re: [GENERAL] Weird performance difference

2017-10-20 Thread Justin Pryzby
On Fri, Oct 20, 2017 at 03:08:26PM -0800, Israel Brewster wrote: > Summary: the following query takes around 12 seconds on my test machine. On > my production machine, it's at half an hour and counting. What's going on? > > which, when run on my test server, has this explain analyze output: >

[GENERAL] Weird performance difference

2017-10-20 Thread Israel Brewster
Summary: the following query takes around 12 seconds on my test machine. On my production machine, it's at half an hour and counting. What's going on?Details:As a first stab at getting some data I need, I've developed the following SQL query:SELECT    legfrom,    count(*) as totaldeps,    count(*)

Re: [GENERAL] How to find out extension directory

2017-10-20 Thread Joe Conway
On 10/20/2017 02:10 PM, Tom Lane wrote: > "David G. Johnston" writes: >> On Fri, Oct 20, 2017 at 1:12 PM, rakeshkumar464 >> wrote: >>> How do I know beforehand where the dir path is ? > >> I think pg_config ( >>

Re: [GENERAL] How to find out extension directory

2017-10-20 Thread Tom Lane
"David G. Johnston" writes: > On Fri, Oct 20, 2017 at 1:12 PM, rakeshkumar464 > wrote: >> How do I know beforehand where the dir path is ? > I think pg_config ( > https://www.postgresql.org/docs/current/static/app-pgconfig.html ) is what >

[GENERAL] Logical replication - behavior of REFRESH PUBLICATION's copy_data option

2017-10-20 Thread Adam LaMore
Hi all, I'm exploring the new PG10 logical replication feature and trying to understand how ALTER SUBSCRIPTION ... REFRESH PUBLICATION works. My planned publication will have over 100 tables, some of which are quite large. If I add a table to the publication, I understand that I have to use the

Re: [GENERAL] How to find out extension directory

2017-10-20 Thread David G. Johnston
On Fri, Oct 20, 2017 at 1:12 PM, rakeshkumar464 wrote: > I am documenting on automating installation of pgaudit extension for > containers. On my laptop I see that the directory where the files > pgaudit.control and pgaudit--1.2.sql needs to be present is > >

Re: [GENERAL] How to find out extension directory

2017-10-20 Thread Melvin Davidson
On Fri, Oct 20, 2017 at 4:12 PM, rakeshkumar464 wrote: > I am documenting on automating installation of pgaudit extension for > containers. On my laptop I see that the directory where the files > pgaudit.control and pgaudit--1.2.sql needs to be present is > >

[GENERAL] How to find out extension directory

2017-10-20 Thread rakeshkumar464
I am documenting on automating installation of pgaudit extension for containers. On my laptop I see that the directory where the files pgaudit.control and pgaudit--1.2.sql needs to be present is /usr/share/postgresql/10/extension. How do I know beforehand where the dir path is ? -- Sent

Re: [GENERAL] tgrm index for word_similarity

2017-10-20 Thread Igal @ Lucee.org
On 10/19/2017 4:54 PM, Igal @ Lucee.org wrote: I want to use Postgres for a fuzzy auto-suggest search field.  As the user will be typing their search phrase, Postgres will show a list of items that fuzzy-matches what they typed so far, ordered by popularity (ntile(20)) and distance, i.e. 1 -

[GENERAL] Replication stops under certain circumstances

2017-10-20 Thread Kim Rose Carlsen
Hi I have some strange issues with a postgresql read replica that seems to stop replicating under certain circumstances. Whenever we have changes to our views we have script that drops all views and reload them from scratch with the new definitions. The reloading of the views happens in a

[GENERAL] Issues shutting down PostgreSQL 10 cleanly

2017-10-20 Thread Stephen Froehlich
I am in the midst of heavy, large write operations on a new database this is currently around 23 billion lines. My max_wal_size is 256 GB and my checkpoint_timeout is 12 hours. (Smaller values were hindering the writes.) Note: this is on Ubuntu 16.04 LTS. I am having trouble shutting down

Re: [GENERAL] How do you decide what aggregates to add?

2017-10-20 Thread Tom Lane
Seamus Abshere writes: > Who decides if a seemingly-useful aggregate is added to Postgres? There's no particularly well-defined process for it, but as far as these items ago: > 1. I just discovered first()/last() as defined in the wiki [1], where > it's noted that conversion

[GENERAL] How do you decide what aggregates to add?

2017-10-20 Thread Seamus Abshere
hi, Who decides if a seemingly-useful aggregate is added to Postgres? I would like to advocate for a couple, but I worry that I'm misunderstanding some community process that has decided _not_ to add aggregates or something. 1. I just discovered first()/last() as defined in the wiki [1], where