Re: [GENERAL] EXCLUDE constraint with not equals

> Given the following table, I would like to ensure that all the rows for an > email that have a user defined map to the same user. > > CREATE TABLE person ( > id INTEGER PRIMARY KEY, > user TEXT, > email TEXT NOT NULL); > > > What I think I'm looking for is something like this: > > CREATE TA

[GENERAL] ANALYZE command question

Hi, Will calling ANALYZE table; twice in a row actually run the command twice ? Or there is some sort of check that if the table is not changed since the time of first call the second command will not actually be run? Thanks, Igor

Re: [GENERAL] ANALYZE command question

Igor Stassiy wrote: > Will calling ANALYZE table; twice in a row actually run the command twice ? > Or there is some sort of > check that if the table is not changed since the time of first call the > second command will not > actually be run? The statistics will be calculated each time you run

[GENERAL] POSTGRES to ORACLE dump

Hello, Is there a way to create a dump file of a Postgres DB to Oracle DB? I've found some ways using general snippets but I would like to know what is the best way to do it. Thanks Regards, __ Oliver G. Dizon Z Getcare Systems Team RTZ Associates, Inc. johnoli...@rtzassoc

Re: [GENERAL] Fwd: Data corruption after restarting replica

Hi Tomasi, sorry for replying so late, but I haven't noticed your email.. >On 16.2.2015 11:44, Novák, Petr wrote: >> Hello, >> >> sorry for posting to second list, but as I've received no reply >> there, I'm trying my luck here. >> >> Thanks >> Petr >> >> >> -- Forwarded message ---

[GENERAL] shared_buffers formula

Hello all! Recently we've been having problems with swap on our postgresql server. It has 125GB of RAM. We've decided to calculate it's memory consumption. To do this we've used the formulas from the official docs [1]. However there is one parameter that seems strange - Shared disk buffers. Accord

Re: [GENERAL] shared_buffers formula

On 2015-03-03 15:06:54 +0400, Alexander Shutyaev wrote: > Recently we've been having problems with swap on our postgresql server. It > has 125GB of RAM. We've decided to calculate it's memory consumption. To do > this we've used the formulas from the official docs [1]. Note that I think those form

Re: [GENERAL] The slave suddenly stopped with such DB log : "will not overwrite a used ItemId" and "heap_insert_redo: failed to add tuple"

2015-03-02 23:21 GMT+08:00 Adrian Klaver : > On 03/02/2015 02:49 AM, hailong Li wrote: > >> >> >> Hi, dear pgsql-general >> >> >> The details are as follows: >> >> *1. environment* >> >> *DB Master* >> >> $ cat /etc/issue >> CentOS release 6.5 (Final) >> Kernel \r on an \m >> >> $ uname -av >> Lin

Re: [GENERAL] POSTGRES to ORACLE dump

You might want to consider pg_dump with the --column-inserts option. [quote from man ] ... --column-inserts, --attribute-inserts Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very slow; it is mainly useful

Re: [GENERAL] Re: [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary

27.02.2015 14:42, Sergey Shchukin пишет: 27.02.2015 14:11, Sergey Shchukin пишет: 27.02.2015 11:52, Jim Nasby пишет: On 2/26/15 12:25 AM, Sergey Shchukin wrote: Hi Radovan ! Thank you for the reply. The question is that this table is not a subject for a massive updates/deletes. Is there any

Re: [GENERAL]

On 03/03/2015 04:39 AM, Ramesh T wrote: hi all, I am using postgres 9.4 on windows 7. i want import that backup sql file into postgres schema. i googled i got copy, but when i ran in windows COPY actor FROM 'c:\users\venu\downloads\sakila-data.sql'; ERROR: invalid input syntax for

[GENERAL] Copy Data between different databases

Hi, I want to copy data between two servers (Version 9.1 and 9.4) I've tried psql -h host1 -U user1 -d db1 -f /q1.sql | psql -h host2 -U user2 -d db2 -f /q2.sql Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY (SELECT ...) TO STDIN As a result nothing is copied. When I run a

[GENERAL]

hi all, I am using postgres 9.4 on windows 7. i want import that backup sql file into postgres schema. i googled i got copy, but when i ran in windows COPY actor FROM 'c:\users\venu\downloads\sakila-data.sql'; ERROR: invalid input syntax for integer: "" CONTEXT: COPY actor, line 1,

Re: [GENERAL] Copy Data between different databases

On 03/03/2015 06:18 AM, Tim Semmelhaack wrote: Hi, I want to copy data between two servers (Version 9.1 and 9.4) I've tried psql -h host1 -U user1 -d db1 -f /q1.sql | psql -h host2 -U user2 -d db2 -f /q2.sql Both sql-scripts include the COPY (SELECT ...) TO STDOUT or COPY (SELECT ...) TO STD

Re: [GENERAL] EXCLUDE constraint with not equals

On Tue, Mar 3, 2015 at 3:27 AM, Emre Hasegeli wrote: > > > Given the following table, I would like to ensure that all the rows for an > > email that have a user defined map to the same user. > > > > CREATE TABLE person ( > > id INTEGER PRIMARY KEY, > > user TEXT, > > email TEXT NOT NULL); >

Re: [GENERAL] Performance on DISABLE TRIGGER

David Steele wrote > > ALTER TABLE requires an exclusive lock - my guess is that another > process has a lock on the table. It could even be a select. > > pg_locks is your friend in this case: > http://www.postgresql.org/docs/9.4/static/view-pg-locks.html Hi David I'm a bit confused on how to

Re: [GENERAL] Performance on DISABLE TRIGGER (resend)

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I'm doing some maintenance - which is done quite often, never had this > problem before - which requires me to disable triggers, run some updates and > then re-enable the triggers. Disabling triggers requires a heavy lock. A better way is to

Re: [GENERAL] EXCLUDE constraint with not equals

On Tue, Mar 3, 2015 at 11:15 AM, Kai Groner wrote: > I found it necessary to add the gist_text_ops opclass for the inequality: > > CREATE TABLE person ( > id INTEGER PRIMARY KEY, > "user" TEXT, > email TEXT NOT NULL, > EXCLUDE USING gist (email WITH =, "user" gist_text_ops WITH <>) >

Re: [GENERAL]

Hi Ramesh: On Tue, Mar 3, 2015 at 1:39 PM, Ramesh T wrote: > i want import that backup sql file into postgres schema. > i googled i got copy, but when i ran in windows > ​If the backup is an SQL file you just ran it with psql. If it is a DATA file, you copy it:​ COPY actor FROM 'c:\users\venu\

Re: [GENERAL] Performance on DISABLE TRIGGER (resend)

Greg Sabino Mullane wrote > Disabling triggers requires a heavy lock. A better way is to use > the session_replication_role feature. See: > > http://blog.endpoint.com/2015/01/postgres-sessionreplication-role.html This is a very effective solution to my problem. Thanks for the tip, Greg. -- Vi

Re: [GENERAL] Copy Data between different databases

Hi Adrian: On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver wrote: > On 03/03/2015 06:18 AM, Tim Semmelhaack wrote: > >> Hi, >> >> I want to copy data between two servers (Version 9.1 and 9.4) >> >> I've tried >> >> ​​ >> psql -h host1 -U user1 -d db1 -f /q1.sql | psql -h host2 -U user2 -d db2 >>

Re: [GENERAL] Copy Data between different databases

Have you considered using dblink() or foreign data wrappers to transfer the data? You can do a select from source, insert into target using one of these methods. RC > On Mar 3, 2015, at 12:09 PM, Francisco Olarte wrote: > > Hi Adrian: > > On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver

[GENERAL] autovacuum worker running amok - and me too ;)

Hi, running postgresql on ubuntu for many years, but now i'm in big trouble. My system has 24GB of real memory but after some hours one autovacuum worker is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the process with kill -9 and the postgresql-server is restarting becaus

Re: [GENERAL] autovacuum worker running amok - and me too ;)

wambacher writes: > My system has 24GB of real memory but after some hours one autovacuum worker > is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the > process with kill -9 and the postgresql-server is restarting because of that > problem. > i changed the base configurati

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Tom Lane-2 wrote > Maybe you could reduce the statistics targets for that table. don't understand what you mean. do you mean how often that table is autovacuumed? at the moment about once a day or once in two days, i think. > I think we've heard that the analyze functions for PostGIS data types a

Re: [GENERAL] autovacuum worker running amok - and me too ;)

wambacher writes: > Tom Lane-2 wrote >> Maybe you could reduce the statistics targets for that table. > don't understand what you mean. do you mean how often that table is > autovacuumed? at the moment about once a day or once in two days, i think. No, I mean the amount of stats detail that ANAL

Re: [GENERAL] Copy Data between different databases

On 03/03/2015 10:09 AM, Francisco Olarte wrote: Hi Adrian: On Tue, Mar 3, 2015 at 4:44 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/03/2015 06:18 AM, Tim Semmelhaack wrote: Hi, I want to copy data between two servers (Version 9.1 and 9.4) I've

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Tom Lane-2 wrote > See ALTER TABLE SET STATISTICS TARGET. thanks, will try it regards walter btw: the postgis analyze problem has been fixed more than one year ago, but i'll ask them too. -- View this message in context: http://postgresql.nabble.com/autovacuum-worker-running-amok-and-me-to

Re: [GENERAL] Sequences not moved to new tablespace

On Tue, Feb 24, 2015 at 10:32:42AM -0500, Tom Lane wrote: > For implementation reasons, ALTER DATABASE SET TABLESPACE refuses the > case where the database already has some tables that have been explicitly > placed into that tablespace. (I forget the exact reason for this, but > it's got something

[GENERAL] Partitioning and constraint exclusion

Howdy, I spent a majority of today playing around with pg_partman (awesome tool btw!). I am mainly using the time-static method with an interval of one month. I wanted to see what performance improvements I could get with some common queries that are used by our analytics team. A lot of these

Re: [GENERAL] autovacuum worker running amok - and me too ;)

On 3/3/2015 6:17 PM, Tom Lane wrote: wambacher writes: My system has 24GB of real memory but after some hours one autovacuum worker is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the process with kill -9 and the postgresql-server is restarting because of that problem. i

Re: [GENERAL] Partitioning and constraint exclusion

Samuel Smith wrote > I noticed that I could get very nice partition elimination using > constant values in the where clause. > > Ex: > select * from > where > > between '2015-01-01' and > '2015-02-15' > > However, I could not get any partition elimination for queries that did > not have