Re: [GENERAL] Install pgAudit extension

[Devrim Gündüz]

Hi,

On Tue, 2016-12-06 at 05:44 +, Dylan Luong wrote:
> I need some advice on installing the pgAudit extension as I am new to
> PostgreSQL extenstions.

Looks like you installed PostgreSQL via community RPMS, so:

https://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgaudit_95-1.0.4-1.rhel6.x8
6_64.rpm

it means, 

yum install pgaudit_95

will do the trick for installing. Then you can create the extension with:

CREATE EXTENSION pgaudit;

You will need to add some lines to postgresql.conf. Sample ones are here:

https://github.com/devrimgunduz/pgextensionconfig/blob/master/9.5/pgaudit.conf

-HTH

Regards,
-- 
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Migrating data from DB2 zOS to PostgreSQL

[Julien Rouhaud]

On Tue, Dec 06, 2016 at 11:06:12AM +0530, Swapnil Vaze wrote:
> Hello,
> 

Hello

> We need some help on how we can migrate data from DB2 zOS database to
> postgres database.
> 
> Are there any utilities present? Any thoughts how we should approach?

You can use this utility: https://github.com/dalibo/db2topg

The README should provide all needed informations.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org


-- 
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] Install pgAudit extension

[John R Pierce]

On 12/5/2016 9:44 PM, Dylan Luong wrote:
As I have Postrgres9.5 installed and downloaded the zip file 
(pgaudit-REL9_5_STABLE.zip), I assume I only have to perform steps 7 
and 8.


if you installed postgres from the yum.postgresql.org repository, try...


yum install pgaudit_95

https://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/repoview/pgaudit_95.html


--
john r pierce, recycling bits in santa cruz

[GENERAL] Install pgAudit extension

[Dylan Luong]

Hi

I need some advice on installing the pgAudit extension as I am new to 
PostgreSQL extenstions.

I currently have Postgres95 on Linux Redhat 6.8 running on the server and I 
need to test out the pgAudt extension.
I downloaded the file pgaudit-REL9_5_STABLE.zip from 
https://github.com/pgaudit/pgaudit/tree/REL9_5_STABLE
In the Readme file, under "Compile and Install" the instructions are:


1.   Clone the PostgreSQL repository:
git clone https://github.com/postgres/postgres.git

2.   Checkout REL9_5_STABLE branch:
git checkout REL9_5_STABLE

3.   Make PostgreSQL:
./configure
make install -s

4.   Change to the contrib directory:
cd contrib

5.   Clone the pgAudit extension:
git clone https://github.com/pgaudit/pgaudit.git

6.   Change to pgAudit directory:
cd pgaudit

7.   Build pgAudit and run regression tests:
make -s check

8.   Install pgAudit:
make install

My problem is that I can't find the contrib directory anywhere on the server. ( 
assume this is because the install steps refers to the old Postfres 8???)
There is default directory for extensions in my current 9.5 install:
/usr/pgsql-9.5/share/extension/

As I have Postrgres9.5 installed and downloaded the zip file 
(pgaudit-REL9_5_STABLE.zip), I assume I only have to perform steps 7 and 8.
Do I just unzip the content into a directory 
/usr/pgsql-9.5/share/extension/pgaudit and then run the steps 7 and 8??? Or do 
I even need to run the make steps as all the files appears to be there. Ie 
.control, .sql, .conf, etc.

The makefile in pgaudit-REL9_5_STABLE.zip refers to .../contrib/pgaudit/. 
?

# contrib/pg_audit/Makefile

MODULE_big = pgaudit
OBJS = pgaudit.o $(WIN32RES)

EXTENSION = pgaudit
DATA = pgaudit--1.0.sql
PGFILEDESC = "pgAudit - An audit logging extension for PostgreSQL"

REGRESS = pgaudit
REGRESS_OPTS = --temp-config=$(top_srcdir)/contrib/pgaudit/pgaudit.conf

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/pgaudit
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

Thanks for your help.
Regards
Dylan

[GENERAL] Migrating data from DB2 zOS to PostgreSQL

[Swapnil Vaze]

Hello,

We need some help on how we can migrate data from DB2 zOS database to
postgres database.

Are there any utilities present? Any thoughts how we should approach?

-- 
Thanks & Regards,
Swapnil Vaze

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

Well yeah, trying to run a PAGER that's not there might throw an error.
Or you know, nothing in PAGER might imply "pager off".

> I find it a bit odd that all of your queries were using the pager...did I 
> miss where you reported that setting?

I didn't report it because I wasn't looking in that direction.   A
PAGER set to blank for login 'doom' and no PAGER setting for login
'postgres' explains much of what I was seeing, I think: selects run as
 'doom' tended to be blank (unless I had a pset no pager somewhere),
selects run as 'postgres' always worked.






On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston
 wrote:
> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner  wrote:
>>
>> And I guess I did that intentionally, my .bashrc has
>>
>>   # I use emacs shells, I got a "pager" already:
>>   export PAGER=''
>>
>
> PAGER= psql --pset=pager=always -c 'select 1;'
> 
>
> Remove PAGER= and I'm good.
>
> I guess that psql could be a bit more helpful by reporting something to
> stderr if the value of PAGER is not an executable (platform dependent...)
>
> I find it a bit odd that all of your queries were using the pager...did I
> miss where you reported that setting?
>
> David J.
>


-- 
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] Select works only when connected from login postgres

[David G. Johnston]

On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner  wrote:

> And I guess I did that intentionally, my .bashrc has
>
>   # I use emacs shells, I got a "pager" already:
>   export PAGER=''
>
>
​PAGER= psql --pset=pager=always -c 'select 1;'​


Remove PAGER= and I'm good.

I guess that psql could be a bit more helpful by reporting something to
stderr if the value of PAGER is not an executable (platform dependent...)

I find it a bit odd that all of your queries were using the pager...did I
miss where you reported that setting?

David J.

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

And I guess I did that intentionally, my .bashrc has

  # I use emacs shells, I got a "pager" already:
  export PAGER=''

On Mon, Dec 5, 2016 at 8:52 PM, Joseph Brenner  wrote:
> Wait, that's not quite right.  The user 'postgres' has no PAGER envar,
> but user 'doom' has an empty value:
>
> PAGER=
>
>
> On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner  wrote:
>>> So what does:
>>>
>>> env | grep PAGER
>>>
>>> show?
>>
>> Nothing.  I have no PAGER settting (I don't normally use one).
>>
>>
>> On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver  
>> wrote:
>>> On 12/05/2016 05:13 PM, Joseph Brenner wrote:
>>>
>>>
 I just went around temporarily undoing things I did while
 straigtening up, and I find there's one thing I can do that
 consistently breaks things: removing my new ~/.psqlrc file.
 In fact, it appears that I need to have a file that exists and
 contains this line:

\pset pager off
>>>
>>>
>>> So what does:
>>>
>>> env | grep PAGER
>>>
>>> show?
>>>
>>>
>>>
>>>
>>> --
>>> 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] Select works only when connected from login postgres

[Joseph Brenner]

Wait, that's not quite right.  The user 'postgres' has no PAGER envar,
but user 'doom' has an empty value:

PAGER=


On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner  wrote:
>> So what does:
>>
>> env | grep PAGER
>>
>> show?
>
> Nothing.  I have no PAGER settting (I don't normally use one).
>
>
> On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver  
> wrote:
>> On 12/05/2016 05:13 PM, Joseph Brenner wrote:
>>
>>
>>> I just went around temporarily undoing things I did while
>>> straigtening up, and I find there's one thing I can do that
>>> consistently breaks things: removing my new ~/.psqlrc file.
>>> In fact, it appears that I need to have a file that exists and
>>> contains this line:
>>>
>>>\pset pager off
>>
>>
>> So what does:
>>
>> env | grep PAGER
>>
>> show?
>>
>>
>>
>>
>> --
>> 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] Select works only when connected from login postgres

[Joseph Brenner]

> So what does:
>
> env | grep PAGER
>
> show?

Nothing.  I have no PAGER settting (I don't normally use one).


On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver  wrote:
> On 12/05/2016 05:13 PM, Joseph Brenner wrote:
>
>
>> I just went around temporarily undoing things I did while
>> straigtening up, and I find there's one thing I can do that
>> consistently breaks things: removing my new ~/.psqlrc file.
>> In fact, it appears that I need to have a file that exists and
>> contains this line:
>>
>>\pset pager off
>
>
> So what does:
>
> env | grep PAGER
>
> show?
>
>
>
>
> --
> 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] WAL File archive time

[Michael Paquier]

On Tue, Dec 6, 2016 at 7:24 AM, Israel Brewster  wrote:
> Simple question: are WAL files archived when full, or when recycled?

When full.

> That is, are the WAL archive files "up-to-date" other than the current WAL 
> file,
> or will the archives always be wal_keep_segments behind?

WAL archives are kept up to date, meaning that all the segments older
than the one being written are archived (well perhaps not if the
archiver lags behind). wal_keep_segments numbers the number of
segments kept in pg_xlog past the last completed checkpoint.
-- 
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] Index size

[Joshua D. Drake]

On 12/03/2016 03:57 PM, Samuel Williams wrote:


With some indexes, it looks like MySQL might not be adding all data to
the index (e.g. ignoring NULL values). Does MySQL ignore null values
in an index? Can we get the same behaviour in Postgres to minimise
usage? What would be the recommendation here?


I don't think you are going to find many answers about the MySQL 
implementation (since it will be storage engine specific and it isn't 
Postgres) on a PostgreSQL list.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[GENERAL] Streaming Replication delay getting bigger

[Patrick B]

Hi guys,

I've got some database servers in USA (own data center) and also @ AWS
Japan.

*USA:*
master01
slave01 (Streaming Replication from master01 + wal_files)
slave02 (Streaming Replication from master01 + wal_files)

*Japan: (Cascading replication)*
slave03 (Streaming Replication from slave02 + wal_files)
slave04 (Streaming Replication from slave02)

*Running this query on slave02:*

select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
---
 00:00:00.802012
(1 row)

*Same query on slave03 and slave04:*

select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
---
 00:56:53.639516
(1 row)


*slave02:*

SELECT client_hostname, client_addr,
pg_xlog_location_diff(pg_stat_replication.sent_location,
pg_stat_replication.replay_location) AS byte_lag FROM pg_stat_replication;
 client_hostname |  client_addr  | byte_lag
-+---+--

 | slave03  |  2097400

 | slave04 |  3803888

(2 rows)


Why is that delay that big? Is it because networking issue? I tried to find
out what the cause is, but couldn't find anything.

SCP and FTP (big files) between those servers are really fast, +1.0MB/s.
I'm using PostgreSQL 9.2.14

Thanks!
Patrick.

[GENERAL] Does PostgreSQL support BIM(Building Information Modeling) storage？

[sunpeng]

Does PostgreSQL support BIM(Building Information Modeling) storage？Or how?
I can only find few infomation:
http://repository.tudelft.nl/islandora/object/uuid:dcb7fc18-208c-4e3b-bc2a-d24a2346d44b?collection=research
Are there any other articles/books/software recommended?
Thanks！
peng

Re: [GENERAL] Select works only when connected from login postgres

[Tom Lane]

Joseph Brenner  writes:
> Okay: I think I'm closing in on the trouble.  I didn't used to
> have a ~/.psqlrc file, but recently I experimented with
> creating one.  When I have a .psqlrc file containing the magic
> incantaion

>\pset pager off

> *Then* everything works.

Ah!  So, most likely, there is something wrong with the local installation
of "more", or whatever the environment variable PAGER is set to.  If you
say "more somefile", does it behave reasonably?  Check "echo $PAGER"
as well.

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] Select works only when connected from login postgres

[Adrian Klaver]

On 12/05/2016 05:13 PM, Joseph Brenner wrote:



I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

   \pset pager off


So what does:

env | grep PAGER

show?




--
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] Select works only when connected from login postgres

[David G. Johnston]

On Mon, Dec 5, 2016 at 6:13 PM, Joseph Brenner  wrote:

> Another oddity I noticed is that I expected that the .psqlrc
> file would not be read at all when using the --command feature,
> but instead I would see messaging that indicated the commands
> in there were being executed


​New behavior in 9.6 - you shouldn't be seeing this in your 9.4 test setup
unless you are​ using the 9.6 psql to connect to the 9.4 server (its a
client-specific behavior).

David J.

Re: [GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

Okay: I think I'm closing in on the trouble.  I didn't used to
have a ~/.psqlrc file, but recently I experimented with
creating one.  When I have a .psqlrc file containing the magic
incantaion

   \pset pager off

*Then* everything works.  All three of my extant postgresql
installations work correctly whether connected to with unix
login doom or postgres.

Our story thus far:

I've got three postgresql installations running on a Debian
stable machine:

  o version 9.4, a binary package from Debian stable
(using port 5432),
  o  a build of 9.6.1 from scratch (using port 5433),
  o  an installation of 9.6.1 from a binary pgdb
 package, (using port 5434).

I've been seeing some odd behavior where a psql connection will
work fine if connected to as *unix login* 'postgres', but not
always if with unix login 'doom', it which case even the
simplest selects can fail silently, without any messages in the
log or on the screen to explain why.

Tom Lane suggested I might try connecting all three of my psql
clients to all three of the servers (by juggling the port and host
options).

Since I was going to conduct at least 9 experiments (with two
logins each), I decided to script it, but before that I made an
effort to clean things up and make sure all three installations
were exactly parallel setups: all needed a user 'doom' with
superuser privileges, all needed a 'doom' database which was
owned by 'doom', all have a pg_hba.conf with auth-method trust,
and so on.  I also added additional logging settings (as
suggested by Tom) to all three the postgresql.conf files.

There were some other small things I changed, such as making
all the log files "chmod a+r" so the script would be able to
read them and echo newly added messages...

And I created a ~/.psqlrc file, though I didn't expect it
to have any effect on my new trial runs using the --command
feature, e.g.:

  /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"

When I got the script cleaned up and working, I found that all
9 connections worked, for both logins: something I'd done has
fixed the problem (or alternately, the problem has "gone away
on it's own").

For example, now when connecting to my local build (without
bothering to specifying port & host):

  doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom

This works now (as does \du, \l, etc):

  select 'hello' as world;
world
   ---
hello

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

   \pset pager off

I thought it might be just the fact that it was non-empty, and
tried a few other settings without any luck.  If I have that
line in my ~/.psqlrc, then this probe returns the expected result:

  /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"

If I delete that line, then the select fails silently again.

Another oddity I noticed is that I expected that the .psqlrc
file would not be read at all when using the --command feature,
but instead I would see messaging that indicated the commands
in there were being executed, e.g.

  Pager usage is off.

Or in the logs:

  2016-12-05 16:17:04 PST [18517-3] doom@doom LOG:  statement: set
client_encoding to 'unicode'

Because I also had this line:

  \encoding unicode




On Sun, Dec 4, 2016 at 9:51 AM, Tom Lane  wrote:
> Joseph Brenner  writes:
>>> So what happens when you specify the port in your psql connection, eg:
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>
>> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
>> other two complain like so:
>
>>   psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket "/tmp/.s.PGSQL.5434"?
>
> What this probably indicates is that the other two installations are
> configured to put their socket files someplace else than /tmp, perhaps
> /var/run/postgresql.  Connecting to them and issuing "show
> unix_socket_directories" would tell the tale.
>
> You can persuade a psql to connect to a socket in a nondefault directory
> by giving the directory name as host, eg
>
> psql --host=/var/run/postgresql -p 5434
>
> It would be interesting to try all nine combinations of the psql's
> supplied by your various installations and the servers, just to confirm
> which ones behave normally and which don't.  Of course, the other two
> would have to be told --host=/tmp to talk to the handbuilt server.
>
> regards, tom lane

Re: [GENERAL] Index size

[Samuel Williams]

Melvin, uh... I'm a software engineer... since when was it a problem to
want to know how things work and why they are different? If you have
nothing to contribute of a relevant technical nature, please don't reply,
I'm really not interested.

[GENERAL] WAL File archive time

[Israel Brewster]

Simple question: are WAL files archived when full, or when recycled? That is, are the WAL archive files "up-to-date" other than the current WAL file, or will the archives always be wal_keep_segments behind?---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD

Re: [GENERAL] Transaction lock granting order

[Joshua Ma]

Thanks a bunch Tom, appreciate the quick response.

On Mon, Dec 5, 2016 at 12:33 PM, Tom Lane  wrote:

> Joshua Ma  writes:
> > Can someone point me to documentation on (or confirm) this detail on
> > Postgres locking?
>
> > - Transaction X starts and acquires a lock on a table T
> > - Transaction Y starts and attempts to acquire a conflicting lock on T -
> it
> > is now blocked
> > - Transaction Z starts and also attempts to acquire a conflicting lock
> on T
> > - it is now blocked
>
> > Is txn Y guaranteed to be the first txn to proceed once X finishes?
>
> In isolation, arrival order is respected, but there are cases where it
> would not be.  In particular, lock queues can get reordered to fix
> "soft deadlock" situations where the only alternative to letting Z go
> ahead of Y is to raise a deadlock error.  This would require there being
> other locks in the system besides the ones you mention, of course.
> (And it may well require more than three transactions --- I don't remember
> at the moment what are the user-visible cases where this happens.)
>
> You can find probably more than you want to know about deadlock handling
> in src/backend/storage/lmgr/README.
>
> regards, tom lane
>

Re: [GENERAL] Transaction lock granting order

[Tom Lane]

Joshua Ma  writes:
> Can someone point me to documentation on (or confirm) this detail on
> Postgres locking?

> - Transaction X starts and acquires a lock on a table T
> - Transaction Y starts and attempts to acquire a conflicting lock on T - it
> is now blocked
> - Transaction Z starts and also attempts to acquire a conflicting lock on T
> - it is now blocked

> Is txn Y guaranteed to be the first txn to proceed once X finishes?

In isolation, arrival order is respected, but there are cases where it
would not be.  In particular, lock queues can get reordered to fix
"soft deadlock" situations where the only alternative to letting Z go
ahead of Y is to raise a deadlock error.  This would require there being
other locks in the system besides the ones you mention, of course.
(And it may well require more than three transactions --- I don't remember
at the moment what are the user-visible cases where this happens.)

You can find probably more than you want to know about deadlock handling
in src/backend/storage/lmgr/README.

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

[GENERAL] Transaction lock granting order

[Joshua Ma]

Can someone point me to documentation on (or confirm) this detail on
Postgres locking?

- Transaction X starts and acquires a lock on a table T
- Transaction Y starts and attempts to acquire a conflicting lock on T - it
is now blocked
- Transaction Z starts and also attempts to acquire a conflicting lock on T
- it is now blocked

Is txn Y guaranteed to be the first txn to proceed once X finishes? Is
there some "lock queue order" that is respected? Is there any chance Z can
proceed before Y, say if it acquires a less aggressive lock?

Put differently: when txn Y starts, it has to wait. Is this wait time at
most the length of the longest txn older than Y?

Thanks,
Josh

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

[Jeff Janes]

On Fri, Dec 2, 2016 at 9:40 AM, Tom DalPozzo  wrote:

> Hi,
> I've two tables, t1 and t2, both with one bigint id indexed field and one
> 256 char data field; t1 has always got 1 row, while t2 is increasing as
> explained in the following.
>
> My pqlib client countinously updates  one row in t1 (every time targeting
> a different row) and inserts a new row in t2. All this in blocks of 1000
> update-insert per commit, in order to get better performance.
> Wal_method is fsync, fsync is on, attached my conf file.
> I've a 3.8ghz laptop with evo SSD.
>
> Performance is  measured every two executed blocks and related to these
> blocks.
>
> Over the first few minutes performance is around 10Krow/s then it slowly
> drops, over next few minutes to 4Krow/s, then it slowly returns high and so
> on, like a wave.
> I don't understand this behaviour. Is it normal? What does it depend on?
>

Yes, that is normal.  It is also very complicated.  It depends on pretty
much everything.  PostgreSQL, kernel, filesystem, IO controller, firmware,
hardware, other things going on on the computer simultaneously, etc.


>
> Also, when I stop the client I see the SSD light still heavily working.
>

This is normal.  It writes out critical data to a WAL log first, and then
leisurely writes out the changes to the actual data files later.  In the
case of a crash, the WAL will be used to replay the data file changes which
may or may not have made it to disk.

It would last quite a while unless I stop the postgresql server, in this
> case it suddenly stops.
>

Do you stop postgresql with fast or immediate shutdown?


> If I restart the server it remains off.
> I'm wondering if it's normal. I'd like to be sure that my data are safe
> once commited.
>

If your kernel/fs/SSD doesn't lie about syncing the data, then your data is
safe once committed. (It is possible there are bugs in PostgreSQL, of
course, but nothing you report indicates you have found one).

If you really want to be sure that the full stack, from PostgreSQL down to
the hardware on the SSD, is crash safe, the only real way is to do some
"pull the plug" tests.

Cheers,

Jeff

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, Martin Collins wrote:


   The problem with kexi is that it does not run on Microsoft OSes which is
what my clients use.


There is a Windows package in the works, apparently. Version 3 already
builds on Windows.


Martin,

  However, it is highly unlikely that my clients (all large industrial
companies) would install anything other than what they buy from Microsoft.
Unfortunate, but true.


Flask has a simple SQLAlchemy wrapper now. It is also based on bootstrap
though I don't know how the widget set compares with Phoenix,


  Based on advice from someone who builds postgres applications for a living
I decided that for my purposes as a non-professional application developer
the most parsimonious approach is to simplify to postgres (model),
wxPython/Phoenix-3.0.2.0 (view), and Python3 (controller) with SQL embedded
in the appropirate methods. This will result in a stand-alone application
that can be packaged for easy installation and use by clients.

Thanks very much for your suggestions,

Rich


--
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] Postgres Traffic accounting

[basti]

I have try the network-analyzer-for-postgresql but I get no data.


On 05.12.2016 14:12, Achilleas Mantzios wrote:
> On 05/12/2016 14:35, William Ivanski wrote:
>>
>> Does it need to be done by listening to network packets? You can get
>> statistics by query and user with pgbadger.
>>
> I guess he'd have to use some tool like this :
> https://www.vividcortex.com/resources/network-analyzer-for-postgresql
> https://www.vividcortex.com/blog/2015/05/13/announcing-vividcortex-network-analyzer-mysql-postgresql/
> 
> This works by listening to the network and then correlate network
> activity with PIDs and then somehow via (pg_stat_* or ps) with queries.
> 
>>
>> Em 9h41 Seg, 05/12/2016, basti
>> <mailingl...@unix-solution.de>
>> escreveu:
>>
>> Hallo,
>>
>> I have to try traffic accounting for postgres using tcpdump and
>> nfdump.
>> I can see what traffic is produced but cant see the query /
>> activity who
>> do it. because there is an ssl connection. use plain text is not
>> an option.
>>
>> I also try to use tcap-postgres. this does not compile on my
>> server and
>> its very old (year 2004).
>>
>> Is there a way to see traffic network of postgres sort by query?
>>
>> best regards
>> basti
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>> -- 
>>
>> William Ivanski
>>
> 
> 
> -- 
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
> 


-- 
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] Postgres and LibreOffice's 'Base'

[Martin Collins]

On 04/12/16 17:54, Rich Shepard wrote:
>
>The problem with kexi is that it does not run on Microsoft OSes which is
> what my clients use.

There is a Windows package in the works, apparently. Version 3 already
builds on Windows.

> I'm also seriously looking at peewee, which is an ORM simpler
> than SQLAlchemy, and the Phoenix toolkit for the UI.

Flask has a simple SQLAlchemy wrapper now. It is also based on bootstrap
though I don't know how the widget set compares with Phoenix,

Martin



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

[GENERAL] How to config start/stop scripts in a different data dir for CentOS7/systemctl/PG9.6

[Edilmar LISTAS]

Hi,

I do these steps to config Postgresql 9.6 in CentOS 6.x using a 
different data directory:

yum -y install postgresql96-server postgresql96-contrib pg_top96
chkconfig postgresql-9.6 on
service postgresql-9.6 initdb
service postgresql-9.6 start
echo "localhost:5432:*:postgres:mypass" > ~/.pgpass
chmod 0600 ~/.pgpass
echo "localhost:5432:*:postgres:mypass" > ~postgres/.pgpass
chmod 0600 ~postgres/.pgpass
chown postgres.postgres ~postgres/.pgpass
su - postgres
psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'mypass';"
exit
#- => my data dir is /sistemas/sat4/bdpg
service postgresql-9.6 stop
cd /etc/rc.d/init.d
ln postgresql-9.6 pgsat
cd /etc/sysconfig/pgsql
echo "PGDATA=/sistemas/sat4/bdpg" > pgsat
mkdir /sistemas/sat4/bdpg
chown postgres.postgres /sistemas/sat4/bdpg
chmod 700 /sistemas/sat4/bdpg
su - postgres
/usr/pgsql-9.6/bin/initdb -D /sistemas/sat4/bdpg
/usr/pgsql-9.6/bin/pg_ctl -D /sistemas/sat4/bdpg -l logfile start
sleep 5000
psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'mypass';"
exit
#-
su - postgres
/usr/pgsql-9.6/bin/pg_ctl -D /sistemas/sat4/bdpg -l logfile stop
exit
service pgsat start
chkconfig --add pgsat
chkconfig pgsat on
chkconfig postgresql-9.6 off
#-

But now CentOS7 uses the new systemd service control system.
I only get to start PG manually like this:
su - postgres
/usr/pgsql-9.6/bin/pg_ctl -D /sistemas/sat4/bdpg -l logfile start





--
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] Bad query? Or planner?

[Devin Smith]

Correct. There is no WHERE filter in the first query. The JOIN condition
from the first query was moved into a WHERE filter in the second query
(enabled by LATERAL). Both have the same ordering applied with a limit of 1.

I chatted with a couple users in the IRC channel, and I think I got the
consensus that the two queries are semantically the same, but the query
planner doesn't currently optimize my original query.

-Devin

On Mon, Dec 5, 2016 at 1:59 AM Johann Spies  wrote:

>
>
> On 28 November 2016 at 21:11, Devin Smith  wrote:
>
> Hi,
>
>
>
> I recently wrote a query that I thought was easy to reason about, and I
> assumed the query planner would execute it efficiently.
>
> SELECT * FROM xtag_stack_feed
> JOIN (
>   SELECT DISTINCT ON (do_post_xtag.xtag_ci) *
>   FROM do_post_xtag
>   JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id
>   ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC)
> last_post
> ON last_post.xtag_ci=xtag_stack_feed.xtag_ci
> ORDER BY decayed_to_base DESC
> LIMIT 1;
>
> Unfortunately, the query as written is not being executed efficiently. I
> tried to rewrite it in a couple different ways without success, and then
> learned about lateral joins. Rewritten as follows, it executes efficiently.
>
> SELECT * FROM xtag_stack_feed
> JOIN LATERAL (
>   SELECT DISTINCT ON (do_post_xtag.xtag_ci) *
>   FROM do_post_xtag
>   JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id
>   WHERE do_post_xtag.xtag_ci=xtag_stack_feed.xtag_ci
>   ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC)
> last_post
> ON true
> ORDER BY decayed_to_base DESC
> LIMIT 1;
>
> From my naive perspective, it seems like the second query is semantically
> equivalent to the first; it just has the join condition moved into the
> subquery as a WHERE filter.
>
>
>
> I do not see a "where"  condition in your first query.
>
> Regards
> Johann
>
>
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you.  (Psalm 63:3)
>

Re: [GENERAL] Postgres Traffic accounting

[basti]

I have try the network-analyzer-for-postgresql but I get no data.

On 05.12.2016 14:12, Achilleas Mantzios wrote:
> On 05/12/2016 14:35, William Ivanski wrote:
>>
>> Does it need to be done by listening to network packets? You can get
>> statistics by query and user with pgbadger.
>>
> I guess he'd have to use some tool like this :
> https://www.vividcortex.com/resources/network-analyzer-for-postgresql
> https://www.vividcortex.com/blog/2015/05/13/announcing-vividcortex-network-analyzer-mysql-postgresql/
> 
> This works by listening to the network and then correlate network
> activity with PIDs and then somehow via (pg_stat_* or ps) with queries.
> 
>>
>> Em 9h41 Seg, 05/12/2016, basti
>> <mailingl...@unix-solution.de>
>> escreveu:
>>
>> Hallo,
>>
>> I have to try traffic accounting for postgres using tcpdump and
>> nfdump.
>> I can see what traffic is produced but cant see the query /
>> activity who
>> do it. because there is an ssl connection. use plain text is not
>> an option.
>>
>> I also try to use tcap-postgres. this does not compile on my
>> server and
>> its very old (year 2004).
>>
>> Is there a way to see traffic network of postgres sort by query?
>>
>> best regards
>> basti
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>> -- 
>>
>> William Ivanski
>>
> 
> 
> -- 
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
> 


-- 
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] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

[Tom DalPozzo]

I tried to tune some parameters without appreciable changes in this
behaviour.
I tried to play with:
checkpoint timeout
wal size
shared buffers
commit delay
checkpoijnt completion target

No meaningful info found in the log file.

Regards



2016-12-04 4:02 GMT+01:00 Tomas Vondra :

> On Fri, 2016-12-02 at 13:45 -0800, Adrian Klaver wrote:
> >
> > On 12/02/2016 09:40 AM, Tom DalPozzo wrote:
> > >
> > >
> > > Hi,
> > > I've two tables, t1 and t2, both with one bigint id indexed field
> > > and
> > > one 256 char data field; t1 has always got 1 row, while t2 is
> > > increasing as explained in the following.
> > >
> > > My pqlib client countinously updates  one row in t1 (every time
> > > targeting a different row) and inserts a new row in t2. All this in
> > > blocks of 1000 update-insert per commit, in order to get better
> > > performance.
> > > Wal_method is fsync, fsync is on, attached my conf file.
> > > I've a 3.8ghz laptop with evo SSD.
> > >
> > > Performance is  measured every two executed blocks and related to
> > > these
> > > blocks.
> > >
> > > Over the first few minutes performance is around 10Krow/s then it
> > > slowly
> > > drops, over next few minutes to 4Krow/s, then it slowly returns
> > > high and
> > > so on, like a wave.
> > > I don't understand this behaviour. Is it normal? What does it
> > > depend on?
> > Have you looked at the Postgres log entries that cover these
> > episodes?
> >
> > Is there anything of interest there?
> >
> In particular look at checkpoints. In the config file you've changed
> checkpoint_timeout, but you haven't changed max_wal_size, so my guess
> is the checkpoints happen every few minutes, and run for about 1/2 the
> time (thanks for completion_target=0.5). That would be consistent with
> pattern of good/bad performance.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>

Re: [GENERAL] postgres pg_restore append data

[Melvin Davidson]

On Mon, Dec 5, 2016 at 4:36 AM, dhanuj hippie 
wrote:

> Hi,
> I have a pg backup created using pg_dump custom format. I'm trying to
> restore into a DB which already has some data, using pg_restore data-only.
> This fails in scenarios where same data (some rows) is present in DB as
> well as dump. Is there a way to ignore such rows and proceed with restoring
> the rest of data ? I don't want to lose the present data in DB. I need to
> append the backup onto current content in DB.
>
> Thanks,
> Dhanuj
>












*The best I can advise you is this.1. Create a new database x.2. Restore
the backup to the new database.3. Rename the table to something like
table_old.4. Rename the primary index and any other index   so that they
will not be the same as ones in the original table.5. Dump table_old only
-> pg_dump -t table_old x > table_old.sql6. Restore table_old to your
database with your original table (table_orig).Then you can:INSERT INTO
table_orig   SELECT * FROM table_old   WHERE your_primary_key NOT IN
(SELECT your_primary_key FROM table_orig );*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Mon, 5 Dec 2016, Johann Spies wrote:


Web2py is another (and maybe simpler) alternative to Django.


Johann,

  Thank you. I'll look at it.

Rich


--
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] Postgres Traffic accounting

[basti]

As I can see pgbadger is a log Analyzer.

I need to know how many traffic (in kb or mb) does a query produce.

On 05.12.2016 13:35, William Ivanski wrote:
> Does it need to be done by listening to network packets? You can get
> statistics by query and user with pgbadger.
> 
> 
> Em 9h41 Seg, 05/12/2016, basti  > escreveu:
> 
> Hallo,
> 
> I have to try traffic accounting for postgres using tcpdump and nfdump.
> I can see what traffic is produced but cant see the query / activity who
> do it. because there is an ssl connection. use plain text is not an
> option.
> 
> I also try to use tcap-postgres. this does not compile on my server and
> its very old (year 2004).
> 
> Is there a way to see traffic network of postgres sort by query?
> 
> best regards
> basti
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> -- 
> 
> William Ivanski
> 


-- 
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] Postgres Traffic accounting

[Achilleas Mantzios]

On 05/12/2016 14:35, William Ivanski wrote:


Does it need to be done by listening to network packets? You can get statistics 
by query and user with pgbadger.


I guess he'd have to use some tool like this :
https://www.vividcortex.com/resources/network-analyzer-for-postgresql
https://www.vividcortex.com/blog/2015/05/13/announcing-vividcortex-network-analyzer-mysql-postgresql/

This works by listening to the network and then correlate network activity with 
PIDs and then somehow via (pg_stat_* or ps) with queries.



Em 9h41 Seg, 05/12/2016, basti > escreveu:

Hallo,

I have to try traffic accounting for postgres using tcpdump and nfdump.
I can see what traffic is produced but cant see the query / activity who
do it. because there is an ssl connection. use plain text is not an option.

I also try to use tcap-postgres. this does not compile on my server and
its very old (year 2004).

Is there a way to see traffic network of postgres sort by query?

best regards
basti


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

--

William Ivanski




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

Re: [GENERAL] Postgres Traffic accounting

[William Ivanski]

Does it need to be done by listening to network packets? You can get
statistics by query and user with pgbadger.

Em 9h41 Seg, 05/12/2016, basti  escreveu:

> Hallo,
>
> I have to try traffic accounting for postgres using tcpdump and nfdump.
> I can see what traffic is produced but cant see the query / activity who
> do it. because there is an ssl connection. use plain text is not an option.
>
> I also try to use tcap-postgres. this does not compile on my server and
> its very old (year 2004).
>
> Is there a way to see traffic network of postgres sort by query?
>
> best regards
> basti
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
-- 

William Ivanski

[GENERAL] Postgres Traffic accounting

[basti]

Hallo,

I have to try traffic accounting for postgres using tcpdump and nfdump.
I can see what traffic is produced but cant see the query / activity who
do it. because there is an ssl connection. use plain text is not an option.

I also try to use tcap-postgres. this does not compile on my server and
its very old (year 2004).

Is there a way to see traffic network of postgres sort by query?

best regards
basti


-- 
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] count(*) in binary mode returns 0

[Daniel Verite]

imagene...@gmail.com wrote:

> nfields: 1
> from_psql  bytes_to_read:4 read:4
> host_order  bytes_to_read:4 read:4
> conv_int ir:0

Note that count(*) produces a bigint (8 bytes), not an int (4 bytes).

=> select pg_typeof(count(1));
 pg_typeof 
---
 bigint
(1 row)

Can you check if your code does work when the query is simply
SELECT 1::bigint ?


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] Extensions and privileges in public schema

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Paul Ramsey
> Sent: Sonntag, 4. Dezember 2016 22:24
> To: Lee Hachadoorian 
> Cc: pgsql-general 
> Subject: Re: [GENERAL] Extensions and privileges in public schema
> 
> When you create the student user, remove their create privs in public.
> Then create a scratch schema and grant them privs there.
> Finally, alter the student user so that the scratch schema appears FIRST in 
> their search path. This will cause
> unqualified CREATE statements to create in the scratch schema.
> For full separation, give each student their own login and set the search 
> path to
> 
> "$user", public
> 
> That way each student gets their own private scratch area, and it is used by 
> default for their creates.
> 
> P
> 
> 
> 
> On Sun, Dec 4, 2016 at 1:10 PM, Lee Hachadoorian 
> 
> > wrote:
> 
> 
>   This question is specifically motivated by my use of the PostGIS 
> extension, but since other extensions create
> functions and other supporting objects in public schema, I believe it is more 
> general.
> 
>   I'm teaching a university-level class using PostGIS. I have created a 
> scratch schema for students to create
> objects in. At the end of the term I can drop scratch and start fresh the 
> following term.
> 
>   Students of course can also create objects in public schema, and often 
> do unintentionally because the forget
> to schema qualify their CREATE TABLE statements. This complicates things 
> because I can't drop public schema without
> dropping various PostGIS (and other) tables and functions. Additionally, 
> while I doubt the students would do
> something like drop a public function or supporting table (like 
> spatial_ref_sys), it nonetheless seems like a poor
> idea for these database objects to be vulnerable.

You could 

REVOKE CREATE ON SCHEMA public FROM public;

So your students would not be able to create objects in the public schema.

Bye
Charles

> 
>   What is considered best practices in this case? Should PostGIS 
> extension be kept in its own schema (as was
> suggested when I asked about this on GIS.SE  )? If I do so, 
> can I treat public schema the way I have
> been using scratch schema, i.e. could I drop and recreate clean public schema 
> at end of term? Should I leave
> extensions in public but limit rights of public role in that schema (so that 
> they don't unintentionally create
> tables there, or accidentally delete other objects)? Or do Postgres DBA's 
> just not worry about the objects in public
> schema, and rely upon applications and login roles to interact with the 
> database intelligently?
> 
>   To be clear, primary goal is to keep student created objects in one 
> schema which can be dropped at the end of
> the term. But the question of preventing accidental creation/deletion of 
> objects in public schema is possibly
> related, and the overall database organization might address both concerns.
> 
>   Best,
>   --Lee
> 
> 
> 
>   --
> 
>   Lee Hachadoorian
>   Assistant Professor of Instruction, Geography and Urban Studies
>   Assistant Director, Professional Science Master's in GIS
>   Temple University
> 




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

[GENERAL] postgres pg_restore append data

[dhanuj hippie]

Hi,
I have a pg backup created using pg_dump custom format. I'm trying to
restore into a DB which already has some data, using pg_restore data-only.
This fails in scenarios where same data (some rows) is present in DB as
well as dump. Is there a way to ignore such rows and proceed with restoring
the rest of data ? I don't want to lose the present data in DB. I need to
append the backup onto current content in DB.

Thanks,
Dhanuj