Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Sat, Feb 18, 2017 at 1:32 PM, Scott Marlowe wrote: > Yes it can. Truncate has been rollbackable for a while now. Per the docs: https://www.postgresql.org/docs/9.6/static/sql-truncate.html "TRUNCATE is transaction-safe with respect to the data in the tables: the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 1:38 PM, Rakesh Kumar wrote: > LOCK TABLE yourtable ; > CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ; > TRUNCATE yourtable; > INSERT INTO yourtable SELECT * from keep; > COMMIT; > === > the above snippet assumes truncate in PG

Re: [GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited

Hi again Adrian, Facepalm... The master server was not installed by me. I was assured by the installer guy that it was version 9.4.1 and 64 bit. Facepalm... I managed to get enough access to that server to discover they had installed the 32 bit version of PostgreSQL. Who knows why? This

Re: [GENERAL] disk writes within a transaction

On Thu, Feb 16, 2017 at 11:33 AM, 2xlp - ListSubscriptions < postg...@2xlp.com> wrote: > Can someone enlighten me to how postgres handles disk writing? I've read > some generic remarks about buffers, but that's about it. > > We have a chunk of code that calls Postgres in a less-than-optimal way

Re: [GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited

On 02/17/2017 12:34 PM, Richard Brosnahan wrote: Thanks for the response Adrian, Both servers are pretty much identical. uname -a master Linux devtmbm178 2.6.32-642.6.2.el6.x86_64 #1 SMP Tue Oct 25 13:37:48 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux slave Linux devtmbm176

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

LOCK TABLE yourtable ; CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ; TRUNCATE yourtable; INSERT INTO yourtable SELECT * from keep; COMMIT; === the above snippet assumes truncate in PG can be in a transaction. In other words, while truncate by itself is atomic, it can't be rolled

[GENERAL] Re: [GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited

Thanks for the response Adrian, Both servers are pretty much identical.  uname -a master Linux devtmbm178 2.6.32-642.6.2.el6.x86_64 #1 SMP Tue Oct 25 13:37:48 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux slave Linux devtmbm176 2.6.32-642.11.1.el6.x86_64 #1 SMP Tue Nov 15 09:40:59 PST 2016 x86_64

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Hi Tim, Am 2017-02-17 um 17:02 schrieb Tim Bellis: The DELETE operations only deletes rows from the > previous day. It's possible that there have been rows > added that day which ought not to be deleted, so > TRUNCATE wouldn't work. OK, then I'll try two other suggestions: - use table

Re: [GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited

On 02/16/2017 04:39 PM, Richard Brosnahan wrote: Hi all, Way back in December I posted a question about mirroring from an RPM installed PostgreSQL (binary) to a source built PostgreSQL, with the same version (9.4.1 --> 9.4.1). Both servers are running OEL6. I went back to the previous threads

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

From: Jeff Janes [mailto:jeff.ja...@gmail.com] Sent: 17 February 2017 02:59 To: Tim Bellis Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis

Re: [GENERAL] "Database does not exist" weirdness

On 02/16/2017 11:18 PM, Michael Tyson wrote: Hi folks, Please excuse the question if I'm missing something stupid, but I seem to be stuck. I've created a postgres database, via an Elixir project setup, and it's showing up via psql's \l command, but I'm seeing "database does not exist"

Re: [GENERAL] "Database does not exist" weirdness

Michael Tyson writes: > Anything I should be looking at, here? What am I missing? > postgres=# \l > List of databases > Name | Owner | Encoding | Collate |Ctype| Access > privileges >

Re: [GENERAL] "Database does not exist" weirdness

On Friday 17 February 2017 18:18:20 Michael Tyson wrote: > postgres=# \q > pi@raspi ~ $ sudo -u postgres psql testdb > psql: FATAL: database "testdb" does not exist > pi@raspi ~ $ sudo -u postgres createdb testdb > createdb: database creation failed: ERROR: duplicate key value violates > unique

[GENERAL] "Database does not exist" weirdness

Hi folks, Please excuse the question if I'm missing something stupid, but I seem to be stuck. I've created a postgres database, via an Elixir project setup, and it's showing up via psql's \l command, but I'm seeing "database does not exist" messages. Transcript of a session showing this

[GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited

Hi all, Way back in December I posted a question about mirroring from an RPM installed PostgreSQL (binary) to a source built PostgreSQL, with the same version (9.4.1 --> 9.4.1). Both servers are running OEL6.  I won't copy the entire thread from before, as the situation has changed a bit.

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

The DELETE operations only deletes rows from the previous day. It's possible that there have been rows added that day which ought not to be deleted, so TRUNCATE wouldn't work. But that was a helpful suggestion - thanks! Tim -Original Message- From: Hannes Erven

Re: [GENERAL] Access privileges /yyyy -- role that granted this privilege.

Ok thank you (and sorry i didn't read this line of documentation) 2017-02-17 15:18 GMT+01:00 Adrian Klaver : > On 02/17/2017 05:44 AM, Jean-Michel Scheiwiler wrote: > >> Hello, >> >> I delve into access privileges and I have a problem (or a >> miscomprehension) when i

Re: [GENERAL] Access privileges /yyyy -- role that granted this privilege.

On 02/17/2017 05:44 AM, Jean-Michel Scheiwiler wrote: Hello, I delve into access privileges and I have a problem (or a miscomprehension) when i type \l, \dn+ or \dp with the / "role that granted this privilege" part. ( https://www.postgresql.org/docs/current/static/sql-grant.html ) \l

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

On 02/17/2017 05:55 AM, Murtuza Zabuawala wrote: Thank you All for your suggestions, But I was looking for solution around COPY command only. Sort of a cheat: https://www.postgresql.org/docs/9.6/static/sql-copy.html PROGRAM A command to execute. In COPY FROM, the input is read from

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

Thank you All for your suggestions, But I was looking for solution around COPY command only. -- Regards, Murtuza Zabuawala EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company On Fri, Feb 17, 2017 at 3:06 PM, Alexander Shchapov wrote: > You might

[GENERAL] Access privileges /yyyy -- role that granted this privilege.

Hello, I delve into access privileges and I have a problem (or a miscomprehension) when i type \l, \dn+ or \dp with the / "role that granted this privilege" part. ( https://www.postgresql.org/docs/current/static/sql-grant.html ) \l for instance [postgres:~]$psql psql (9.6.2) Type "help"

Re: [GENERAL] Streaming Replication Without Downtime

Gabriel you are thinking this in the correct way, but its really : pg_basebackup -D --write-recovery-conf --progress --xlog-method=stream -h then you just edit recovery.conf (if needed), tweak postgersql.conf (if needed) and start the standby . On 17/02/2017 15:09, Gunnar "Nick" Bluth

Re: [GENERAL] Streaming Replication Without Downtime

(sorry for the toppost, mobile device) What you're looking for is pg_basebackup with - - xlog=stream, I guess. Regards, Nick Am 17. Februar 2017 14:06:36 MEZ schrieb Gabriel Ortiz Lour : >Hi all, >I've been searching for a way to initialize a new Hot Standby node

[GENERAL] Streaming Replication Without Downtime

Hi all, I've been searching for a way to initialize a new Hot Standby node with Streaming Replication withou the need for stop or even restarting the master. Of course the master is already with the needed SR configs. I know I have to use pg_start_backup/pg_stop_backup, but i'd like some

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Hi Tim, Am 2017-02-15 um 18:30 schrieb Tim Bellis: I have a postgres 9.3.4 database table which (intermittently but reliably) > gets into a state where queries get blocked indefinitely > [..] Notes: - This database table is used for about 6 million row writes per day, > all of which

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

You might want to look into pgloader: http://pgloader.io/ On Fri, Feb 17, 2017 at 7:26 AM, Murtuza Zabuawala wrote: > Hi, > > Is there any way to load multiple CSV files at once using single COPY > command? > > I have scenario where I have to load multiple