Re: [GENERAL] Fwd: I could not see any row in audit table

On 01/25/2017 09:57 AM, Shailesh Singh wrote: Dear Adrian Klaver, I have used pg_dump to take backup eg: pg_dump my_db > /dbbackup/audit_table.sql To be clear you dumped the entire database, not just the audit table, correct? Meant to ask before, are you dumping/restoring between the same

Re: [GENERAL] Tips on maintaining several pg_hba files

On Thu, Jan 26, 2017 at 2:36 AM, Alfredo Palhares wrote: > I have a PostgreSQL cluster with several ROLES that access the node > according to his state. > > On the master node, I only want the roles that need to write and the admins. > On the synchronous node i would want

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

On 01/25/2017 10:47 PM, John R Pierce wrote: On 1/25/2017 12:59 PM, Thomas Kellerer wrote: So here is my question: how does Postgres estimate/know the memory needed for the aggregation? Or does it dynamically resize the memory if the initial assumption was wrong? my understanding is it fits

[GENERAL] Re: How does Postgres estimate the memory needed for sorting/aggregating

Tomas Vondra schrieb am 25.01.2017 um 22:46: I guess this is based on the column statistics stored in pg_stats, but I am not sure: It is based on the average length of values in that column, yes. Thanks for confirming that. I assume this is taken from pg_stats.avg_width ? I'm not sure

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

On 1/25/2017 12:59 PM, Thomas Kellerer wrote: So here is my question: how does Postgres estimate/know the memory needed for the aggregation? Or does it dynamically resize the memory if the initial assumption was wrong? my understanding is it fits as much as it can into a work_mem sized

Re: [GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

On 01/25/2017 09:59 PM, Thomas Kellerer wrote: There was a question on dba.stackexchange recently: http://dba.stackexchange.com/a/162117/1822 That question (and the answer) deals with performance difference of a query caused by the _declared_ length of a VARCHAR column in SQL Server

[GENERAL] How does Postgres estimate the memory needed for sorting/aggregating

There was a question on dba.stackexchange recently: http://dba.stackexchange.com/a/162117/1822 That question (and the answer) deals with performance difference of a query caused by the _declared_ length of a VARCHAR column in SQL Server (everything else being equal - especially the actual

Re: [GENERAL] psql only works with -h (even localhost)

On 01/25/2017 09:35 AM, Tom Lane wrote: Steve Crawford writes: Adrian asks the correct questions. Lacking the answers to those I'm going to venture a guess that a Unix-domain socket exists but access via Unix-domain sockets is somehow blocked, probably by

Re: [GENERAL] Fwd: I could not see any row in audit table

Dear Adrian Klaver, I have used pg_dump to take backup eg: pg_dump my_db > /dbbackup/audit_table.sql And I have restored it using psql command eg: psql my_test_db < /dbbackup/audit_table.sql before it has had also created necessay hstore extension for my new db " *my_test_db*" There is no

[GENERAL] Tips on maintaining several pg_hba files

Hello everyone, I have a PostgreSQL cluster with several ROLES that access the node according to his state. On the master node, I only want the roles that need to write and the admins. On the synchronous node i would want only roles with read access that require the most up to date data, and

Re: [GENERAL] psql only works with -h (even localhost)

Steve Crawford writes: > Adrian asks the correct questions. Lacking the answers to those I'm going > to venture a guess that a Unix-domain socket exists but access via > Unix-domain sockets is somehow blocked, probably by pg_hba.conf. Actually, the more common

Re: [GENERAL] psql only works with -h (even localhost)

On Wed, Jan 25, 2017 at 11:07 AM, Adrian Klaver wrote: > On 01/25/2017 08:02 AM, ProPAAS DBA wrote: > >> Hi all; >> >> >> we have a client server where 'psql -h localhost' is the only way psql >> will connect when ssh'd onto the db server. Would like to be able to >>

Re: [GENERAL] psql only works with -h (even localhost)

Adrian asks the correct questions. Lacking the answers to those I'm going to venture a guess that a Unix-domain socket exists but access via Unix-domain sockets is somehow blocked, probably by pg_hba.conf. >From the psql man page: "...Not all of these options are required; there are useful

Re: [GENERAL] psql only works with -h (even localhost)

On 01/25/2017 08:02 AM, ProPAAS DBA wrote: Hi all; we have a client server where 'psql -h localhost' is the only way psql will connect when ssh'd onto the db server. Would like to be able to just run psql but not sure what the issue/fix is. Anyone have any insight hot to fix this? What is

[GENERAL] psql only works with -h (even localhost)

Hi all; we have a client server where 'psql -h localhost' is the only way psql will connect when ssh'd onto the db server. Would like to be able to just run psql but not sure what the issue/fix is. Anyone have any insight hot to fix this? Thanks in advance -- Sent via pgsql-general

Re: [GENERAL] Fwd: I could not see any row in audit table

On 01/25/2017 03:03 AM, Shailesh Singh wrote: Dear Group Member , I had configured the audit trigger for my datbase following the below document url: *https://wiki.postgresql.org/wiki/Audit_trigger_91plus * Now my audit table :

Re: [GENERAL] Fwd: I could not see any row in audit table

On 25/01/17 11:03, Shailesh Singh wrote: Dear Group Member , I had configured the audit trigger for my datbase following the below document url: *https://wiki.postgresql.org/wiki/Audit_trigger_91plus * Now my audit table : CREATE

[GENERAL] Fwd: I could not see any row in audit table

Dear Group Member , I had configured the audit trigger for my datbase following the below document url: *https://wiki.postgresql.org/wiki/Audit_trigger_91plus * Now my audit table : CREATE TABLE audit.logged_actions ( event_id

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

On 25/01/17 20:14, Johann Spies wrote: On 25 January 2017 at 08:32, Gavin Flower > wrote: What is 'shapefile'? I don't recall ever coming across that term!https://en.wikipedia.org/wiki/Shapefile See

Re: [GENERAL] Searching array for multiple items

På onsdag 25. januar 2017 kl. 09:47:56, skrev Thomas Kellerer < spam_ea...@gmx.net >: Alex Magnum schrieb am 25.01.2017 um 09:29: > I can search an array with 1 = ANY('{1,3,4,7}'::int[]) > > I need to check for one or multiple items in the array. > > e.g.'1,7,3'  =

Re: [GENERAL] Searching array for multiple items

Alex Magnum schrieb am 25.01.2017 um 09:29: > I can search an array with 1 = ANY('{1,3,4,7}'::int[]) > > I need to check for one or multiple items in the array. > > e.g.'1,7,3' = ANY('{1,3,4,7}'::int[] > > I do need to check if > a) all items exist in the array You can use the contains (or is

Re: [GENERAL] Searching array for multiple items

On Wed, Jan 25, 2017 at 11:29 AM, Alex Magnum wrote: > Hi, > I can search an array with 1 = ANY('{1,3,4,7}'::int[]) > > I need to check for one or multiple items in the array. > > e.g. '1,7,3' = ANY('{1,3,4,7}'::int[] > > I do need to check if > a) all items exist in the

[GENERAL] Searching array for multiple items

Hi, I can search an array with 1 = ANY('{1,3,4,7}'::int[]) I need to check for one or multiple items in the array. e.g. '1,7,3' = ANY('{1,3,4,7}'::int[] I do need to check if a) all items exist in the array b) at least one item exists in the array Is there a an operator that allows me to do