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

[Michael Paquier]

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 truncation will be safely rolled back if the surrounding
transaction does not commit."
In short yes a transaction doing a truncate can be rollbacked.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Marlowe]

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 can be in a transaction. In other 
> words, while truncate by itself
> is atomic, it can't be rolled back. So in the above case, if "INSERT INTO 
> yourtable SELECT * from keep;" and
> we rollback, will it rollback yourtable.


Yes it can. Truncate has been rollbackable for a while now.

begin;
create table
insert into table
truncate old table
. something goes wrong .
rollback;

Unless I misunderstand your meaning.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Richard Brosnahan]

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 explains 
everything about my issues with the 64 bit PostgreSQL on the slave. It's 
difficult to get access to our servers, so try not to blame me and think "Why 
didn't he do that first?" Still, I should have tried harder to get access.

In the PostgreSQL documentation, it clearly states that the two servers have to 
be the same architecture (both 32 bit or both 64 bit). Further, when Google 
searching for the errors I see, I find a number of people with similar issues, 
and they were fighting with 32 bit vs 64 bit PostgreSQLs. 

I wasted a LOT of time trying to track this down. I'm sorry I wasted other 
people's time too. 

Anyhow, I uninstalled PostgreSQL on the slave, and reinstalled the 32 bit 
version. Then I followed the instructions for setting up the slave, and it all 
works.

Plenty to do, including setting up proper monitoring, and documentation. It's 
great we have a hot standby, but if nobody knows how to use it in case the 
master goes away, it's not so great. 

THANK YOU for your assistance!


> On Feb 17, 2017, at 10:43 AM, Adrian Klaver  wrote:
> 
> 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 and I could not find if you ever said 
> whether the two systems are using the same hardware architecture or not? 
> Vincent Veyron asked but I can't find a response.
> 
>> 
>> I won't copy the entire thread from before, as the situation has changed
>> a bit. The biggest changes are that I have root on the slave,
>> temporarily, and I've installed PostgreSQL on the slave using yum (also
>> binary).
>> 
>> I've followed all the instructions found here:
>> 
>> https://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION
>> 
>> 
>> The slave is running PostgreSQL 9.4.11 and was installed using yum.
>> It runs fine after I've run initdb and set things up. The master was
>> also installed from rpm binaries, but the installers used Puppet. That
>> version is 9.4.1. Yes, I know I should be using the exact same version,
>> but I couldn't find 9.4.1 in the PostgreSQL yum repo.
>> 
>> 
>> When I replace its data directory as part of the mirroring instructions,
>> using pg_basebackup, PostgreSQL won't start. I used pg_basebackup.
>> 
>> 
>> I get a checksum error, from pg_ctl.
>> 
>> 2016-12-15 08:27:14.520 PST >FATAL: incorrect checksum in control file
>> 
>> 
>> Previously, Tom Lane suggested I try this:
>> 
>> You could try using pg_controldata to compare the pg_control contents;
>> 
>> it should be willing to print field values even if it thinks the checksum
>> 
>> is bad. It would be interesting to see (a) what the master's
>> 
>> pg_controldata prints about its pg_control, (b) what the slave's
>> 
>> pg_controldata prints about pg_control from a fresh initdb there, and
>> 
>> (c) what the slave's pg_controldata prints about the copied pg_control.
>> 
>> 
>> For Tom's requests (a and b), I can provide good output from
>> pg_controldata from the master with production data, and from the slave
>> right after initdb. I'll provide that on request.
>> 
>> 
>> for Tom's request (c) I get this from the slave, after data is copied.
>> 
>> $ pg_controldata
>> 
>> WARNING: Calculated CRC checksum does not match value stored in file.
>> 
>> Either the file is corrupt, or it has a different layout than this program
>> 
>> is expecting.  The results below are untrustworthy.
>> 
>> 
>> Segmentation fault (core dumped)
>> 
>> 
>> With this new installation on the slave, same result. core dump
>> 
>> 
>> Tom Lane then suggested:
>> 
>> $ gdb path/to/pg_controldata
>> 
>> gdb> run /apps/database/postgresql-data
>> 
>> (wait
>> 
>> for it to report segfault)
>> 
>> gdb> bt
>> 
>> 
>> Since I now have gdb, I can do that:
>> 
>> $ gdb /usr/pgsql-9.4/bin/pg_controldata
>> 
>> -bash: gdb: command not found
>> 
>> -bash-4.1$ gdb /usr/pgsql-9.4/bin/pg_controldata
>> 
>> GNU gdb (GDB) Red Hat Enterprise Linux (7.2-90.el6)
>> 
>> Copyright (C) 2010 Free Software Foundation, Inc.
>> 
>> License GPLv3+: GNU GPL version 3 or later
>> 
>> 
>> This is free software: you are free to change and redistribute it.
>> 
>> There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
>> 
>> and "show warranty" for details.
>> 
>> This GDB was configured as "x86_64-redhat-linux-gnu".
>> 
>> For bug reporting instructions, please see:
>> 
>> ...
>> 
>> 

Re: [GENERAL] disk writes within a transaction

[Jeff Janes]

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
> within a transaction block.  I'm wondering where to prioritize fixing it,
> as the traffic on the wire isn't an issue.
>
> Basically the code looks like this:
>
> begin;
> update foo set foo.a='1' where foo.bar = 1;
> ...
> update foo set foo.b='2' where foo.bar = 1;
> ...
> update foo set foo.c='3' where foo.bar = 1;
> commit;
>
> If the updates are likely to be a memory based operation, consolidating
> them can wait.  If they are likely to hit the disk, I should schedule
> refactoring this code sooner than later.
>

You are going to generate more volume of WAL data, which has to reach disk
eventually.  Although it is likely they will all be consolidated into about
the same number of physical writes and syncs.

You are also likely to inhibit the Heap-only-tuple mechanism, because you
will end up with 4 copies of the row which all have to fit in the same
block.  If they don't, it has to migrate some of them to a different block
plus do index maintenance, so you will generate more dirty blocks that
way.  How many more depends on how many indexes you have, and whether the
columns being updated are themselves included in indexes.

There is also a CPU issue when the same tuple is updated repeatedly in a
single transaction.  Each update has to wade through all the previous row
versions, so it is an N^2 operation in the number of updates.

It will probably be easier to refactor the code than to quantify just how
much damage it does.

cheers,

Jeff

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

[Adrian Klaver]

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 2.6.32-642.11.1.el6.x86_64 #1 SMP Tue Nov 15 09:40:59
PST 2016 x86_64 x86_64 x86_64 GNU/Linux

Since the last message, I've downgraded PostgreSQL to 9.4.1 on the
slave, using
rpm -Uvh --oldpackage [file names]

I had wisely kept copies of the rpm files for PostgreSQL 9.4.1 for OEL6
and used those. rpm did the downgrade without issue, and I tested the
9.4.1 PostgreSQL installation. The minimal testing I did after the
install worked fine. initdb, start the server, psql, etc.

I then stopped the new slave PostgreSQL instance, and proceeded with the
instructions for creating a slave.
I again used pg_basebackup

postgres $ pg_basebackup -D /var/lib/pgsql/9.4/data
--write-recovery-conf -h devtmbm178.unix.gsm1900.org -U pgrepuser -p 5432 -W


NOTICE:  pg_stop_backup complete, all required WAL segments have been
archived


This executed without incident.


After verifying, and modifying postgresql.conf, recovery.conf I
attempted to start postgresql. This was again, not successful.


postgres $ pg_ctl start

server starting

-bash-4.1$ < 2017-02-17 12:13:53.176 PST >FATAL:  incorrect checksum in
control file


postgres $ pg_controldata

WARNING: Calculated CRC checksum does not match value stored in file.

Either the file is corrupt, or it has a different layout than this program

is expecting.  The results below are untrustworthy.


Segmentation fault (core dumped)


Now I'm really unhappy. Same server architecture, same PostgreSQL
versions. No joy!


Well something is different about the two Postgres instances. I have 
lost track of where they came from, but can you parse out the compile 
options to each. Suspicion is one is compiled with:


https://www.postgresql.org/docs/9.4/static/install-procedure.html

--disable-integer-datetimes


and one is not, which is the default. You can usually use pg_controldata 
to find that:


Date/time type storage:   64-bit integers

Can you use pg_controldata or is it still seg faulting?

If not that then some other compile option.

Just had another thought.

Is there more then one version of Postgres installed on the slave server?





--

Richard Brosnahan



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rakesh Kumar]

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 back. So in the above case, if "INSERT INTO 
yourtable SELECT * from keep;" and
we rollback, will it rollback yourtable.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Richard Brosnahan]

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 x86_64 x86_64 GNU/Linux

Since the last message, I've downgraded PostgreSQL to 9.4.1 on the slave, using 
rpm -Uvh --oldpackage [file names]

I had wisely kept copies of the rpm files for PostgreSQL 9.4.1 for OEL6 and 
used those. rpm did the downgrade without issue, and I tested the 9.4.1 
PostgreSQL installation. The minimal testing I did after the install worked 
fine. initdb, start the server, psql, etc.

I then stopped the new slave PostgreSQL instance, and proceeded with the 
instructions for creating a slave. 
I again used pg_basebackup
postgres $ pg_basebackup -D /var/lib/pgsql/9.4/data --write-recovery-conf -h 
devtmbm178.unix.gsm1900.org -U pgrepuser -p 5432 -W

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

This executed without incident. 

After verifying, and modifying postgresql.conf, recovery.conf I attempted to 
start postgresql. This was again, not successful.

postgres $ pg_ctl start
server starting
-bash-4.1$ < 2017-02-17 12:13:53.176 PST >FATAL:  incorrect checksum in control 
file

postgres $ pg_controldata
WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting.  The results below are untrustworthy.

Segmentation fault (core dumped)

Now I'm really unhappy. Same server architecture, same PostgreSQL versions. No 
joy!


--

Richard Brosnahan

On Feb 17, 2017, at 10:43 AM, Adrian Klaver  wrote:

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 and I could not find if you ever 
said whether the two systems are using the same hardware architecture or 
not? Vincent Veyron asked but I can't find a response.



I won't copy the entire thread from before, as the situation has changed
a bit. The biggest changes are that I have root on the slave,
temporarily, and I've installed PostgreSQL on the slave using yum (also
binary).

I've followed all the instructions found here:

https://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION


The slave is running PostgreSQL 9.4.11 and was installed using yum.
It runs fine after I've run initdb and set things up. The master was
also installed from rpm binaries, but the installers used Puppet. That
version is 9.4.1. Yes, I know I should be using the exact same version,
but I couldn't find 9.4.1 in the PostgreSQL yum repo.


When I replace its data directory as part of the mirroring instructions,
using pg_basebackup, PostgreSQL won't start. I used pg_basebackup.


I get a checksum error, from pg_ctl.

2016-12-15 08:27:14.520 PST >FATAL: incorrect checksum in control file


Previously, Tom Lane suggested I try this:

You could try using pg_controldata to compare the pg_control contents;

it should be willing to print field values even if it thinks the checksum

is bad. It would be interesting to see (a) what the master's

pg_controldata prints about its pg_control, (b) what the slave's

pg_controldata prints about pg_control from a fresh initdb there, and

(c) what the slave's pg_controldata prints about the copied pg_control.


For Tom's requests (a and b), I can provide good output from
pg_controldata from the master with production data, and from the slave
right after initdb. I'll provide that on request.


for Tom's request (c) I get this from the slave, after data is copied.

$ pg_controldata

WARNING: Calculated CRC checksum does not match value stored in file.

Either the file is corrupt, or it has a different layout than this program

is expecting. The results below are untrustworthy.


Segmentation fault (core dumped)


With this new installation on the slave, same result. core dump


Tom Lane then suggested:

$ gdb path/to/pg_controldata

gdb> run /apps/database/postgresql-data

(wait

for it to report segfault)

gdb> bt


Since I now have gdb, I can do that:

$ gdb /usr/pgsql-9.4/bin/pg_controldata

-bash: gdb: command not found

-bash-4.1$ gdb /usr/pgsql-9.4/bin/pg_controldata

GNU gdb (GDB) Red Hat Enterprise Linux (7.2-90.el6)

Copyright (C) 2010 Free Software Foundation, Inc.

License GPLv3+: GNU GPL version 3 or later


This is free software: you are free to change and redistribute it.

There is NO WARRANTY, to the extent permitted by law. Type "show copying"

and "show warranty" for details.

This GDB was configured as "x86_64-redhat-linux-gnu".

For bug reporting instructions, please see:

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

[Hannes Erven]

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 partitioning ( 
https://www.postgresql.org/docs/9.3/static/ddl-partitioning.html )


- if the number of rows you need to keep is small, you could try 
something like this:

LOCK TABLE yourtable ;
CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ;
TRUNCATE yourtable;
INSERT INTO yourtable SELECT * from keep;
COMMIT;


Best regards,

-hannes



-Original Message-
From: Hannes Erven [mailto:han...@erven.at]
Sent: 17 February 2017 11:47
To: pgsql-general@postgresql.org
Cc: Tim Bellis 
Subject: 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 are then deleted at the end of the day.

If you are dumping the contents of the table anyways, why not use TRUNCATE 
instead of DELETE? It unlinks and recreates the table data files, requiring 
nearly zero IO and analyzing.
Or even drop, and recreate the table with the correct new structure so you do 
not even need to ALTER TABLE ?

I'm a bit skeptical of these suggestions since very competent people have already 
answered your post and did not come up with this... ;-) the only drawback I'm aware of is 
that TRUNCATE will immediatly free disk space on the OS level, so the table's space will 
not be "blocked". But probably the VACUUM you are currently performing will 
also eventually release the unused disk space, so this may or may not match the current 
behaviour.


Best regards,

-hannes







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 and I could not find if you ever 
said whether the two systems are using the same hardware architecture or 
not? Vincent Veyron asked but I can't find a response.




I won't copy the entire thread from before, as the situation has changed
a bit. The biggest changes are that I have root on the slave,
temporarily, and I've installed PostgreSQL on the slave using yum (also
binary).

I've followed all the instructions found here:

https://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION


The slave is running PostgreSQL 9.4.11 and was installed using yum.
It runs fine after I've run initdb and set things up. The master was
also installed from rpm binaries, but the installers used Puppet. That
version is 9.4.1. Yes, I know I should be using the exact same version,
but I couldn't find 9.4.1 in the PostgreSQL yum repo.


When I replace its data directory as part of the mirroring instructions,
using pg_basebackup, PostgreSQL won't start. I used pg_basebackup.


I get a checksum error, from pg_ctl.

2016-12-15 08:27:14.520 PST >FATAL: incorrect checksum in control file


Previously, Tom Lane suggested I try this:

You could try using pg_controldata to compare the pg_control contents;

it should be willing to print field values even if it thinks the checksum

is bad. It would be interesting to see (a) what the master's

pg_controldata prints about its pg_control, (b) what the slave's

pg_controldata prints about pg_control from a fresh initdb there, and

(c) what the slave's pg_controldata prints about the copied pg_control.


For Tom's requests (a and b), I can provide good output from
pg_controldata from the master with production data, and from the slave
right after initdb. I'll provide that on request.


for Tom's request (c) I get this from the slave, after data is copied.

$ pg_controldata

WARNING: Calculated CRC checksum does not match value stored in file.

Either the file is corrupt, or it has a different layout than this program

is expecting.  The results below are untrustworthy.


Segmentation fault (core dumped)


With this new installation on the slave, same result. core dump


Tom Lane then suggested:

$ gdb path/to/pg_controldata

gdb> run /apps/database/postgresql-data

(wait

for it to report segfault)

gdb> bt


Since I now have gdb, I can do that:

$ gdb /usr/pgsql-9.4/bin/pg_controldata

-bash: gdb: command not found

-bash-4.1$ gdb /usr/pgsql-9.4/bin/pg_controldata

GNU gdb (GDB) Red Hat Enterprise Linux (7.2-90.el6)

Copyright (C) 2010 Free Software Foundation, Inc.

License GPLv3+: GNU GPL version 3 or later


This is free software: you are free to change and redistribute it.

There is NO WARRANTY, to the extent permitted by law.  Type "show copying"

and "show warranty" for details.

This GDB was configured as "x86_64-redhat-linux-gnu".

For bug reporting instructions, please see:

...

Reading symbols from /usr/pgsql-9.4/bin/pg_controldata...(no debugging
symbols found)...done.

Missing separate debuginfos, use: debuginfo-install
postgresql94-server-9.4.11-1PGDG.rhel6.x86_64

(gdb) run /var/lib/pgsql/9.4/data

Starting program: /usr/pgsql-9.4/bin/pg_controldata /var/lib/pgsql/9.4/data

WARNING: Calculated CRC checksum does not match value stored in file.

Either the file is corrupt, or it has a different layout than this program

is expecting.  The results below are untrustworthy.



Program received signal SIGSEGV, Segmentation fault.

0x0033d20a3a15 in __strftime_internal () from /lib64/libc.so.6

(gdb) bt

#0  0x0033d20a3a15 in __strftime_internal () from /lib64/libc.so.6

#1  0x0033d20a5a36 in strftime_l () from /lib64/libc.so.6

#2  0x004015c7 in ?? ()

#3  0x0033d201ed1d in __libc_start_main () from /lib64/libc.so.6

#4  0x00401349 in ?? ()

#5  0x7fffe518 in ?? ()

#6  0x001c in ?? ()

#7  0x0002 in ?? ()

#8  0x7fffe751 in ?? ()

#9  0x7fffe773 in ?? ()

#10 0x in ?? ()

(gdb)


pg_controldata shouldn't be core dumping.


Should I give up trying to use 9.4.1 and 9.4.11 as master/slave?

My options appear to be

1 upgrade the master to 9.4.11, which will be VERY DIFFICULT given its
Puppet install, and the difficulty I have getting root access to our
servers.

2 Downgrade the slave. This is easier than option 1, but I would need to
find a yum repo that has that version.

3 Make what I have work, somehow.

Any assistance would be greatly appreciated!

--

Richard Brosnahan




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make 

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

[Tim Bellis]

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 
> wrote:
I have a postgres 9.3.4 database table which (intermittently but reliably) gets 
into a state where queries get blocked indefinitely (at least for many hours) 
behind an automatic vacuum. I was under the impression that vacuum should never 
take any blocking locks for any significant period of time, and so would like 
help resolving the issue.

The process blocking the query is:
postgres 21985 11304 98 Feb13 ?1-14:20:52 postgres: autovacuum worker 
process   
which is running the query
autovacuum: VACUUM public.

Are you sure it doesn't really say:

autovacuum: VACUUM public. (to prevent wraparound)
[Tim Bellis] It doesn’t. I was using the query from 
https://wiki.postgresql.org/wiki/Lock_Monitoring and looking at the 
‘current_statement_in_blocking_process’ column. Is there a different query I 
should be using?

If it doesn't include the "to prevent wraparound", then it should sacrifice 
itself as soon as it realizes it is blocking something else.  If it is not 
doing that, something is wrong.

If it does say "(to prevent wraparound)", then see all the other comments on 
this thread.


Notes:
  - This database table is used for about 6 million row writes per day, all of 
which are then deleted at the end of the day.

How many transactions do those 6 million writes comprise?
[Tim Bellis] I’m not sure - I think it’s 6 million transactions, but there 
might be some batching going on that I’m unaware of. What would this affect? (I 
can dig in if necessary)
(I might have been slightly wrong in characterising the exact behaviour; the 
table might be cleared every hour rather than every day, but there are still 6 
million writes per day)


  - Our application kicks off a manual vacuum against this table each night 
which doesn't hit this problem, as far as we're aware.

If it were having problems, would you be aware of it?  Do you see in the log 
files the completion of the vacuum?  Or look in pg_stat_user_tables to see when 
last_vacuum was.  If it runs every night and succeeds, it is hard to see why 
wraparound would ever kick in.  Unless you are hitting 150,000,000 transactions 
in a day.
[Tim Bellis] I shall investigate this.

Cheers,

Jeff

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

[Adrian Klaver]

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" messages.

Transcript of a session showing this follows below.

Anything I should be looking at, here? What am I missing?


In addition to what has already been asked, I am assuming from the 
system prompts this is happening on a Raspberry Pi, correct?


If so what is being used for storage and have there been any issues with 
said storage, eg someone pulling a SD card out at the wrong time?





Many thanks in advance,
Michael



pi@raspi ~ $ sudo -u postgres psql
psql (9.4.10)
Type "help" for help.

postgres=# \l
 List of databases
 Name |  Owner   | Encoding |   Collate   |Ctype| Access 
privileges
--+--+--+-+-+---
 testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres
 +
  |  |  | | | 
postgres=CTc/postgres+
  |  |  | | | 
testdb=CTc/postgres
 postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
 template0| postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres   
   +
  |  |  | | | 
postgres=CTc/postgres
 template1| postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres   
   +
  |  |  | | | 
postgres=CTc/postgres
(4 rows)

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 
constraint "pg_database_datname_index"
DETAIL:  Key (datname)=(testdb) already exists.








Sent from my iPhone







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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 
> --+--+--+-+-+---
>  testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres  
>+
>   |  |  | | | 
> postgres=CTc/postgres+
>   |  |  | | | 
> testdb=CTc/postgres
>  postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | 

Given the weird spacing you're showing there, I'm suspicious that that
database isn't actually named "testdb", but has some nonprinting
character(s) in its name that confuse psql's formatting code.

An easy way to check for nonprinting characters is to do

set bytea_output to escape;
select datname::bytea from pg_database;

and see if bytea prints anything funny.

Having said that, I don't think that theory explains this result:

> pi@raspi ~ $ sudo -u postgres createdb testdb
> createdb: database creation failed: ERROR:  duplicate key value violates 
> unique constraint "pg_database_datname_index"
> DETAIL:  Key (datname)=(testdb) already exists.

There's definitely something weird there, because what you ought to get
in normal cases is

createdb: database creation failed: ERROR:  database "testdb" already exists

There may be some corruption in pg_database_datname_index that is allowing
the initial search to not find "testdb" but then the insertion decides
there's a conflict.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alan Hodgson]

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 constraint "pg_database_datname_index" DETAIL:  Key
> (datname)=(testdb) already exists.
> 

Something's seriously borked in your PostgreSQL data files. The message it 
should give you if the database exists is:

createdb: database creation failed: ERROR:  database "testdb" already exists

It looks like it partially exists in the catalog but not really. I can't guess 
how you got to such a state, but you should probably nuke your data directory 
and start over with a fresh initdb.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] "Database does not exist" weirdness

[Michael Tyson]

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 follows below.

Anything I should be looking at, here? What am I missing?

Many thanks in advance,
Michael



pi@raspi ~ $ sudo -u postgres psql
psql (9.4.10)
Type "help" for help.

postgres=# \l
 List of databases
 Name |  Owner   | Encoding |   Collate   |Ctype| Access 
privileges 
--+--+--+-+-+---
 testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres
 +
  |  |  | | | 
postgres=CTc/postgres+
  |  |  | | | 
testdb=CTc/postgres
 postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0| postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres   
   +
  |  |  | | | 
postgres=CTc/postgres
 template1| postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres   
   +
  |  |  | | | 
postgres=CTc/postgres
(4 rows)

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 
constraint "pg_database_datname_index"
DETAIL:  Key (datname)=(testdb) already exists.








Sent from my iPhone




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Richard Brosnahan]

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. 
The biggest changes are that I have root on the slave, temporarily, and I've 
installed PostgreSQL on the slave using yum (also binary).

I've followed all the instructions found here:
https://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION

The slave is running PostgreSQL 9.4.11 and was installed using yum. It runs 
fine after I've run initdb and set things up. The master was also installed 
from rpm binaries, but the installers used Puppet. That version is 9.4.1. Yes, 
I know I should be using the exact same version, but I couldn't find 9.4.1 in 
the PostgreSQL yum repo. 

When I replace its data directory as part of the mirroring instructions, using 
pg_basebackup, PostgreSQL won't start. I used pg_basebackup. 

I get a checksum error, from pg_ctl.
2016-12-15 08:27:14.520 PST >FATAL: incorrect checksum in control file

Previously, Tom Lane suggested I try this:
You could try using pg_controldata to compare the pg_control contents;
it should be willing to print field values even if it thinks the checksum
is bad. It would be interesting to see (a) what the master's
pg_controldata prints about its pg_control, (b) what the slave's
pg_controldata prints about pg_control from a fresh initdb there, and
(c) what the slave's pg_controldata prints about the copied pg_control.

For Tom's requests (a and b), I can provide good output from pg_controldata 
from the master with production data, and from the slave right after initdb. 
I'll provide that on request.

for Tom's request (c) I get this from the slave, after data is copied.
$ pg_controldata
WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting.  The results below are untrustworthy.

Segmentation fault (core dumped)

With this new installation on the slave, same result. core dump

Tom Lane then suggested:
$ gdb path/to/pg_controldata
gdb> run /apps/database/postgresql-data
(wait
for it to report segfault)
gdb> bt

Since I now have gdb, I can do that:
$ gdb /usr/pgsql-9.4/bin/pg_controldata
-bash: gdb: command not found
-bash-4.1$ gdb /usr/pgsql-9.4/bin/pg_controldata
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-90.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /usr/pgsql-9.4/bin/pg_controldata...(no debugging symbols 
found)...done.
Missing separate debuginfos, use: debuginfo-install 
postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
(gdb) run /var/lib/pgsql/9.4/data
Starting program: /usr/pgsql-9.4/bin/pg_controldata /var/lib/pgsql/9.4/data
WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting.  The results below are untrustworthy.


Program received signal SIGSEGV, Segmentation fault.
0x0033d20a3a15 in __strftime_internal () from /lib64/libc.so.6
(gdb) bt
#0  0x0033d20a3a15 in __strftime_internal () from /lib64/libc.so.6
#1  0x0033d20a5a36 in strftime_l () from /lib64/libc.so.6
#2  0x004015c7 in ?? ()
#3  0x0033d201ed1d in __libc_start_main () from /lib64/libc.so.6
#4  0x00401349 in ?? ()
#5  0x7fffe518 in ?? ()
#6  0x001c in ?? ()
#7  0x0002 in ?? ()
#8  0x7fffe751 in ?? ()
#9  0x7fffe773 in ?? ()
#10 0x in ?? ()
(gdb)

pg_controldata shouldn't be core dumping. 

Should I give up trying to use 9.4.1 and 9.4.11 as master/slave? 

My options appear to be
1 upgrade the master to 9.4.11, which will be VERY DIFFICULT given its Puppet 
install, and the difficulty I have getting root access to our servers.
2 Downgrade the slave. This is easier than option 1, but I would need to find a 
yum repo that has that version. 
3 Make what I have work, somehow. 

Any assistance would be greatly appreciated!
--

Richard Brosnahan

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

[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.

But that was a helpful suggestion - thanks!

Tim

-Original Message-
From: Hannes Erven [mailto:han...@erven.at] 
Sent: 17 February 2017 11:47
To: pgsql-general@postgresql.org
Cc: Tim Bellis 
Subject: 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 are then deleted at the end of the day.

If you are dumping the contents of the table anyways, why not use TRUNCATE 
instead of DELETE? It unlinks and recreates the table data files, requiring 
nearly zero IO and analyzing.
Or even drop, and recreate the table with the correct new structure so you do 
not even need to ALTER TABLE ?

I'm a bit skeptical of these suggestions since very competent people have 
already answered your post and did not come up with this... ;-) the only 
drawback I'm aware of is that TRUNCATE will immediatly free disk space on the 
OS level, so the table's space will not be "blocked". But probably the VACUUM 
you are currently performing will also eventually release the unused disk 
space, so this may or may not match the current behaviour.


Best regards,

-hannes



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jean-Michel Scheiwiler]

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 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" for help.
>>
>> postgres=# create role superman login superuser;
>> CREATE ROLE
>> postgres=# create role user01 login ;
>> CREATE ROLE
>> postgres=# create role user02 login ;
>> CREATE ROLE
>> postgres=# create database db001;
>> CREATE DATABASE
>> postgres=# \l db001
>>   List of databases
>>  Name  |  Owner   | Encoding |   Collate   |Ctype| Access
>> privileges
>> ---+--+--+-+-+--
>> -
>>  db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> (1 row)
>>
>> postgres=# grant connect on database db001 to user01 ;
>> GRANT
>> postgres=# \l db001
>> List of databases
>>  Name  |  Owner   | Encoding |   Collate   |Ctype|   Access
>> privileges
>> ---+--+--+-+-+--
>> -
>>  db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
>> +
>>|  |  | | |
>> postgres=CTc/postgres+
>>|  |  | | |
>> user01=c/postgres
>> (1 row)
>>
>> postgres=# \q
>> [postgres:~]$psql -U superman postgres
>> psql (9.6.2)
>> Type "help" for help.
>>
>> postgres=# grant connect on database db001 to user02;
>> GRANT
>> postgres=# \l db001
>> List of databases
>>  Name  |  Owner   | Encoding |   Collate   |Ctype|   Access
>> privileges
>> ---+--+--+-+-+--
>> -
>>  db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
>> +
>>|  |  | | |
>> postgres=CTc/postgres+
>>|  |  | | |
>> user01=c/postgres+
>>|  |  | | |
>> user02=c/postgres
>> (1 row)
>>
>>
>> I thought i would get   user02=c/superman but instead i
>> get user02=c/postgres => I don't get the "role that granted this
>> privilege" but i get the owner of the database.
>>
>
> As a practical matter it does not matter as postgres and superman are both
> superusers, still for an explanation of why it happens:
>
> https://www.postgresql.org/docs/9.6/static/sql-grant.html
>
> "If a superuser chooses to issue a GRANT or REVOKE command, the command is
> performed as though it were issued by the owner of the affected object. In
> particular, privileges granted via such a command will appear to have been
> granted by the object owner. (For role membership, the membership appears
> to have been granted by the containing role itself.)"
>
>
>> The problem is the same with schemas or tables access privileges.
>>
>> Can you help me figure this out ?
>>
>
> What are you trying to achieve?
>
>
>
>> Thank you in advance
>>
>> Jean-Michel Scheiwiler
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[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 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" for help.

postgres=# create role superman login superuser;
CREATE ROLE
postgres=# create role user01 login ;
CREATE ROLE
postgres=# create role user02 login ;
CREATE ROLE
postgres=# create database db001;
CREATE DATABASE
postgres=# \l db001
  List of databases
 Name  |  Owner   | Encoding |   Collate   |Ctype| Access
privileges
---+--+--+-+-+---
 db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# grant connect on database db001 to user01 ;
GRANT
postgres=# \l db001
List of databases
 Name  |  Owner   | Encoding |   Collate   |Ctype|   Access
privileges
---+--+--+-+-+---
 db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
+
   |  |  | | |
postgres=CTc/postgres+
   |  |  | | | user01=c/postgres
(1 row)

postgres=# \q
[postgres:~]$psql -U superman postgres
psql (9.6.2)
Type "help" for help.

postgres=# grant connect on database db001 to user02;
GRANT
postgres=# \l db001
List of databases
 Name  |  Owner   | Encoding |   Collate   |Ctype|   Access
privileges
---+--+--+-+-+---
 db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
+
   |  |  | | |
postgres=CTc/postgres+
   |  |  | | |
user01=c/postgres+
   |  |  | | | user02=c/postgres
(1 row)


I thought i would get   user02=c/superman but instead i
get user02=c/postgres => I don't get the "role that granted this
privilege" but i get the owner of the database.


As a practical matter it does not matter as postgres and superman are 
both superusers, still for an explanation of why it happens:


https://www.postgresql.org/docs/9.6/static/sql-grant.html

"If a superuser chooses to issue a GRANT or REVOKE command, the command 
is performed as though it were issued by the owner of the affected 
object. In particular, privileges granted via such a command will appear 
to have been granted by the object owner. (For role membership, the 
membership appears to have been granted by the containing role itself.)"




The problem is the same with schemas or tables access privileges.

Can you help me figure this out ?


What are you trying to achieve?



Thank you in advance

Jean-Michel Scheiwiler



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 standard 
output of the command, and in COPY TO, the output is written to the 
standard input of the command.


Note that the command is invoked by the shell, so if you need to 
pass any arguments to shell command that come from an untrusted source, 
you must be careful to strip or escape any special characters that might 
have a special meaning for the shell. For security reasons, it is best 
to use a fixed command string, or at least avoid passing any user input 
in it.





--
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 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 files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV
HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV
HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a
good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there
any other
> way to do this using single COPY command?
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



--
Alexander Shchapov





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Murtuza Zabuawala]

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 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 files,
> >
> > COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > ..
> > ..
> > ..
> > ..
> > COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV
> HEADER Y
> >
> > 50 files -> 50 COPY command, In my use case I think this is not a good
> way
> > to load data, Can you suggest any better way to do this?
> >
> > I can always write external script (eg: shell script) but is there any
> other
> > way to do this using single COPY command?
> >
> > --
> > Regards,
> > Murtuza Zabuawala
> > EnterpriseDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
>
>
>
> --
> Alexander Shchapov
>

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

[Jean-Michel Scheiwiler]

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" for help.

postgres=# create role superman login superuser;
CREATE ROLE
postgres=# create role user01 login ;
CREATE ROLE
postgres=# create role user02 login ;
CREATE ROLE
postgres=# create database db001;
CREATE DATABASE
postgres=# \l db001
  List of databases
 Name  |  Owner   | Encoding |   Collate   |Ctype| Access
privileges
---+--+--+-+-+---
 db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# grant connect on database db001 to user01 ;
GRANT
postgres=# \l db001
List of databases
 Name  |  Owner   | Encoding |   Collate   |Ctype|   Access
privileges
---+--+--+-+-+---
 db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
+
   |  |  | | |
postgres=CTc/postgres+
   |  |  | | | user01=c/postgres
(1 row)

postgres=# \q
[postgres:~]$psql -U superman postgres
psql (9.6.2)
Type "help" for help.

postgres=# grant connect on database db001 to user02;
GRANT
postgres=# \l db001
List of databases
 Name  |  Owner   | Encoding |   Collate   |Ctype|   Access
privileges
---+--+--+-+-+---
 db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
+
   |  |  | | |
postgres=CTc/postgres+
   |  |  | | |
user01=c/postgres+
   |  |  | | | user02=c/postgres
(1 row)


I thought i would get   user02=c/superman but instead i get user02=c/postgres
=> I don't get the "role that granted this privilege" but i get the owner
of the database.

The problem is the same with schemas or tables access privileges.

Can you help me figure this out ?

Thank you in advance

Jean-Michel Scheiwiler

Re: [GENERAL] Streaming Replication Without Downtime

[Achilleas Mantzios]

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 wrote:

(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 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 
tips, or a link to some tutorial, with the order of the steps.

  I assume will be something like:

-  configure Slave for SR
-  pg_start_backup()
-  rsync PGDATA to slave
-  start PG on the slave
-  pg_stop_backup()

  Anything i'm thinking wrong?

Thanks in advance,
Gabriel


--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet. 



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] Streaming Replication Without Downtime

[Gunnar "Nick" Bluth]

(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 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
>tips, or a link to some tutorial, with the order of the steps.
>
>  I assume will be something like:
>
>-  configure Slave for SR
>-  pg_start_backup()
>-  rsync PGDATA to slave
>-  start PG on the slave
>-  pg_stop_backup()
>
>  Anything i'm thinking wrong?
>
>Thanks in advance,
>Gabriel

-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

[GENERAL] Streaming Replication Without Downtime

[Gabriel Ortiz Lour]

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
tips, or a link to some tutorial, with the order of the steps.

  I assume will be something like:

-  configure Slave for SR
-  pg_start_backup()
-  rsync PGDATA to slave
-  start PG on the slave
-  pg_stop_backup()

  Anything i'm thinking wrong?

Thanks in advance,
Gabriel

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

[Hannes Erven]

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 are then deleted at the end of the day.

If you are dumping the contents of the table anyways, why not use 
TRUNCATE instead of DELETE? It unlinks and recreates the table data 
files, requiring nearly zero IO and analyzing.
Or even drop, and recreate the table with the correct new structure so 
you do not even need to ALTER TABLE ?


I'm a bit skeptical of these suggestions since very competent people 
have already answered your post and did not come up with this... ;-) the 
only drawback I'm aware of is that TRUNCATE will immediatly free disk 
space on the OS level, so the table's space will not be "blocked". But 
probably the VACUUM you are currently performing will also eventually 
release the unused disk space, so this may or may not match the current 
behaviour.



Best regards,

-hannes



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alexander Shchapov]

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 files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there any other
> way to do this using single COPY command?
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



-- 
Alexander Shchapov


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general