date:20181030

Is there a way to speed up WAL replay?

2018-10-30 Thread Torsten Förtsch

Hi,

I am working on restoring a database from a base backup + WAL. With the
default settings the database replays about 3-4 WAL files per second. The
startup process takes about 65% of a CPU and writes data with something
between 50 and 100 MB/sec.

Is there a way to speed that up? The disk can easily sustain 400-500 MB/sec.

Thanks,
Torsten

Re: Fwd: Log file

2018-10-30 Thread Igor Korot

Now is there a command to flush the log - delete the content of it?

All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.


On Wed, Oct 31, 2018 at 12:32 AM Igor Korot  wrote:
>
> Hi, Tom,
>
> On Mon, Oct 29, 2018 at 5:08 PM Tom Lane  wrote:
> >
> > Igor Korot  writes:
> > > On Mon, Oct 29, 2018 at 1:56 PM Tom Lane  wrote:
> > >> You can set up the log files as readable by the OS group of the server
> > >> (see log_file_mode), and then grant membership in that group to whichever
> > >> OS accounts you trust.  You may also need to move the log directory
> > >> out from under $PGDATA to make that work, since PG doesn't like
> > >> world-readable data directories.
> >
> > > I'm trying to make the log file of PG readable of the user who logs in
> > > to the current
> > > OS session. I don't need a write permission, just read.
> > > Because my program will not be started from the "postgres" account.
> >
> > Well, any such setup is a serious security hole in itself, because
> > there is likely to be sensitive data in the postmaster log, eg
> > passwords.  (Remember that the log file is global to the whole cluster,
> > it will not contain just data relevant to the current session.)
> > You should only grant access to people who you trust at more or less
> > the level of trust you'd put in the installation DBA.
> >
> > It may be that these concerns are all irrelevant to you because it's
> > a single-user installation anyway, but they're not irrelevant to
> > people running multi-user installations.  So that's why you can't
> > get Postgres to do it.  In a single-user installation, maybe you
> > should just launch the postmaster as that user.
> >
> > regards, tom lane
>
> OK, I understand.
>
> Thank you.

Re: Fwd: Log file

2018-10-30 Thread Igor Korot

Hi, Tom,

On Mon, Oct 29, 2018 at 5:08 PM Tom Lane  wrote:
>
> Igor Korot  writes:
> > On Mon, Oct 29, 2018 at 1:56 PM Tom Lane  wrote:
> >> You can set up the log files as readable by the OS group of the server
> >> (see log_file_mode), and then grant membership in that group to whichever
> >> OS accounts you trust.  You may also need to move the log directory
> >> out from under $PGDATA to make that work, since PG doesn't like
> >> world-readable data directories.
>
> > I'm trying to make the log file of PG readable of the user who logs in
> > to the current
> > OS session. I don't need a write permission, just read.
> > Because my program will not be started from the "postgres" account.
>
> Well, any such setup is a serious security hole in itself, because
> there is likely to be sensitive data in the postmaster log, eg
> passwords.  (Remember that the log file is global to the whole cluster,
> it will not contain just data relevant to the current session.)
> You should only grant access to people who you trust at more or less
> the level of trust you'd put in the installation DBA.
>
> It may be that these concerns are all irrelevant to you because it's
> a single-user installation anyway, but they're not irrelevant to
> people running multi-user installations.  So that's why you can't
> get Postgres to do it.  In a single-user installation, maybe you
> should just launch the postmaster as that user.
>
> regards, tom lane

OK, I understand.

Thank you.

Re: pg_restore to new database wants to wipe out the old database?

2018-10-30 Thread Adrian Klaver


On 10/30/18 3:25 PM, Ron wrote:

On 10/30/2018 05:16 PM, David G. Johnston wrote:
On Tue, Oct 30, 2018 at 3:09 PM Ron > wrote:


Hi,

v9.6.9

Why is pg_restore trying to drop my production database, when I
(think I) am telling it to create the new database "Molson"?

Straight from the pg_restore documentation:
-C

"When this option is used, the database named with |-d| is used only 
to issue the initial |DROP DATABASE| and |CREATE DATABASE| commands. 
All data is restored into the database name that appears in the archive."


So I've got to explicitly CREATE DATABASE "Molson" and then
pg_restore -d Molson Molson


No. The docs have an example that demonstrates:

https://www.postgresql.org/docs/9.6/static/app-pgrestore.html

To reload the dump into a new database called newdb:

$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump

So(you will probably need to add appropriate -U to below):

createdb -T template0 "Molson"
pg_restore -d "Molson" proddb





Also
" pg_restore -vcC --if-exists --jobs=8 -d postgres Molson "

pg_restore [connection-option...] [option...] [filename]

"Molson" is a file name; pg_restore doesn't use the file name aside 
from finding where the data you want to restore is located.


David J.



--
Angular momentum makes the world go 'round.



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

Full-text Search - Thesaurus relationships

2018-10-30 Thread Nicolas Paris

Hi,

The documentation[1] says thesaurus can include informations of terms
relationships such broader terms, preferred terms ...

I haven't been able to find out how to exploit those relationship in
postgres. Is there any keyword to and associated syntax to make use of
them ?

Thanks,


[1]: 
https://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-THESAURUS
-- 
nicolas

Re: pg_restore to new database wants to wipe out the old database?

2018-10-30 Thread David G. Johnston

On Tue, Oct 30, 2018 at 3:09 PM Ron  wrote:

> Hi,
>
> v9.6.9
>
> Why is pg_restore trying to drop my production database, when I (think I)
> am telling it to create the new database "Molson"?
>

Straight from the pg_restore documentation:

-C

"When this option is used, the database named with -d is used only to issue
the initial DROP DATABASE and CREATE DATABASE commands. All data is
restored into the database name that appears in the archive."

Also
" pg_restore -vcC --if-exists --jobs=8 -d postgres Molson "

pg_restore [connection-option...] [option...] [filename]

"Molson" is a file name; pg_restore doesn't use the file name aside from
finding where the data you want to restore is located.

David J.

pg_restore to new database wants to wipe out the old database?

2018-10-30 Thread Ron


Hi,

v9.6.9

Why is pg_restore trying to drop my production database, when I (think I) am 
telling it to create the new database "Molson"?


$ cd /backup
$ pg_dump -d proddb -j 8 -Fd --no-synchronized-snapshots -Z0 -v -f proddb 2> 
proddb_pgdump.log


$ mv proddb Molson
$ pg_restore -vcC --if-exists --jobs=8 -d postgres Molson
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE proddb
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 145485; 1262 16385 DATABASE 
proddb TAP
pg_restore: [archiver (db)] could not execute query: ERROR:  database 
"proddb" is being accessed by other users

DETAIL:  There are 3 other sessions using the database.
    Command was: DROP DATABASE IF EXISTS "proddb";

pg_restore: processing item 145482 ENCODING ENCODING
pg_restore: processing item 145483 STDSTRINGS STDSTRINGS
pg_restore: processing item 145484 SEARCHPATH SEARCHPATH
pg_restore: processing item 145485 DATABASE proddb
pg_restore: creating DATABASE "proddb"
pg_restore: [archiver (db)] could not execute query: ERROR:  database 
"proddb" already exists
    Command was: CREATE DATABASE "proddb" WITH TEMPLATE = template0 
ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

Thanks


--
Angular momentum makes the world go 'round.

Re: Question about servicescript for stopping and starting postgresql instance

2018-10-30 Thread Adrian Klaver


On 10/30/18 2:09 PM, Marian Forums wrote:



Hi,
Postgres was installed through a repo with yum install.


So did you disable the start/stop scripts that the package installed?


I will try to run it with another type.
Regards
Marian


Op 30 okt. 2018 om 21:56 heeft Adrian Klaver  het 
volgende geschreven:


On 10/30/18 9:27 AM, Marian Forums wrote:
Hi Adrain,
Thanks for your reply.



How was Postgres installed on this machine?

Have you tried the script from the docs?



My script looks like this:


I am no systemd expert, still the Type=forking does not seem right to me.


# cat /usr/lib/systemd/system/postgresql-9.6.pg-tstxxx.service
[Unit]
Description=postgresql_pg-tstxxx
After=syslog.target
After=network.target
[Service]
Type=forking
User=postgresql
Group=postgresqlg
Environment=PGDATA=/data/pg-tstxxx/data-01/
# Run ExecStartPre with root-permissions
PermissionsStartOnly=true
ExecStartPre=/bin/chown postgresql:postgresqlgroup /var/run/postgresql
ExecStart=/usr/pgsql-9.6/bin/pg_ctl start -D ${PGDATA}
ExecStop=/usr/pgsql-9.6/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.6/bin/pg_ctl reload -D ${PGDATA} -s
Restart=on-failure
KillMode=control-group
ExecStop=/bin/kill -SIGTERM $MAINPID
RestartSec=10s
[Install]
WantedBy=multi-user.target
thanks in advance.
regards,
Marian
Op di 30 okt. 2018 om 14:30 schreef Adrian Klaver mailto:adrian.kla...@aklaver.com>>:
On 10/30/18 5:09 AM, Marian Forums wrote:
 > Hi,
 >
 > I have a question about creating a service script to
start/stop/status
 > postgresql instance ( version 9.6.8) on Red Hat 7.
How did you install Postgres on the machine?
 >
 > I have read the explanation of how to create such a service
script on
 > https://www.postgresql.org/docs/9.6/static/server-start.html .
Did you use the example at the above link or did you write your own?
If you wrote your own can you show it?
 >
 > Stopping and Starting works.
 > However when I stop the service with:
 > systemctl stop postgresq.service and than ask for the status with
 > systemctl status postgresql.service, the service gets started again.
 >
 > Is this normal functionality on Red Hat 7?
 > I have searched on the Internet ( including the Red Hat site) for a
 > parameter to replace this behavior, but did not find anything.
 >
 > Your help is much appreciated.
 > Thanks in advance.
 > Regards,
 > Marian
-- Adrian Klaver
adrian.kla...@aklaver.com 



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



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

Re: editable spreadsheet style interface

2018-10-30 Thread Tim Clarke


On 30/10/2018 20:32, Martin Mueller wrote:


I have used Aqua Data Studio for several years. Jetbrains recently 
released a similar product. Academic licensing is affordable (~ $200 a 
year) and very cheap if considered in terms of the time it saves you.


*From: *David Gauthier 
*Date: *Tuesday, October 30, 2018 at 2:06 PM
*To: *"pgsql-gene...@postgresql.org" 
*Subject: *editable spreadsheet style interface

I think I know the answer to this one but I'll ask anyway...

Is there a spreadsheet style interface to a PG DB where users can...

- lock records

- edit records

- submit changes (transaction)

Is there any after-market tool for PG that does something like this ?



Have a look here https://www.postgresql.org/download/product-categories/ 
under "Administration/Development". I'm using pgAdmin right now.



Tim Clarke

Re: Question about servicescript for stopping and starting postgresql instance

2018-10-30 Thread Adrian Klaver


On 10/30/18 9:27 AM, Marian Forums wrote:

Hi Adrain,
Thanks for your reply.



How was Postgres installed on this machine?

Have you tried the script from the docs?



My script looks like this:


I am no systemd expert, still the Type=forking does not seem right to me.


# cat /usr/lib/systemd/system/postgresql-9.6.pg-tstxxx.service
[Unit]
Description=postgresql_pg-tstxxx
After=syslog.target
After=network.target

[Service]
Type=forking
User=postgresql
Group=postgresqlg
Environment=PGDATA=/data/pg-tstxxx/data-01/
# Run ExecStartPre with root-permissions
PermissionsStartOnly=true
ExecStartPre=/bin/chown postgresql:postgresqlgroup /var/run/postgresql
ExecStart=/usr/pgsql-9.6/bin/pg_ctl start -D ${PGDATA}
ExecStop=/usr/pgsql-9.6/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.6/bin/pg_ctl reload -D ${PGDATA} -s
Restart=on-failure
KillMode=control-group
ExecStop=/bin/kill -SIGTERM $MAINPID
RestartSec=10s

[Install]
WantedBy=multi-user.target

thanks in advance.
regards,
Marian

Op di 30 okt. 2018 om 14:30 schreef Adrian Klaver 
mailto:adrian.kla...@aklaver.com>>:


On 10/30/18 5:09 AM, Marian Forums wrote:
 > Hi,
 >
 > I have a question about creating a service script to
start/stop/status
 > postgresql instance ( version 9.6.8) on Red Hat 7.

How did you install Postgres on the machine?

 >
 > I have read the explanation of how to create such a service
script on
 > https://www.postgresql.org/docs/9.6/static/server-start.html .

Did you use the example at the above link or did you write your own?

If you wrote your own can you show it?


 >
 > Stopping and Starting works.
 > However when I stop the service with:
 > systemctl stop postgresq.service and than ask for the status with
 > systemctl status postgresql.service, the service gets started again.
 >
 > Is this normal functionality on Red Hat 7?
 > I have searched on the Internet ( including the Red Hat site) for a
 > parameter to replace this behavior, but did not find anything.
 >
 > Your help is much appreciated.
 > Thanks in advance.
 > Regards,
 > Marian


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: rw_redis_fdw: SQL Errors when statement is within a function

2018-10-30 Thread Adrian Klaver


On 10/30/18 9:15 AM, GPT wrote:

Good afternoon!

...
Postgres did not behave in a 'poor' way, the extension just did not
interpret the results correctly.

Eh! Eh! Adrian/Christoph one minute please because this is something
new (at least in the very clear way you formulate it now and I can
understand it easily)!
The statement was sent correctly from the module to PG; PG returned
the correct set of data to the module; BUT module failed to
interpret/present the data to me!

Q1: Is this what you are telling me?
   a) Yes
   b) No


a) Yes



Q2: Module sent the statement to the PG in a way A which PG does
understand. Did the PG sent back to the module in a way A or B or ...
which module understands?
   a) Yes
   b) No


Not sure of exactly where the breakdown in communication happened, that 
would need input from the extension developer. From what I gather the 
extension reparse's the query at some point and failed to take into 
account that the internal representation of the query may change after a 
number of repetitions of the query.






Q3: And which part has "triggered" (so, is responsible for) the wrong
errors to appear on my screen,
   a) PG,
   b) the module,
   c) the java driver,
   d) PL/SQL,
   e) undefined,
   f) NULL or
   g) I do not know / I do not respond (joke!)?


b) the module.

See below for explanation:
https://github.com/nahanni/rw_redis_fdw/commit/05f5f3247569e6c428360cc4270606a91e57c6ff






... Postgres has no way of knowing what
happens after its data is passed on.

In this case, I do not disagree at all!


You are looking for Postgres to
follow its responses all the way through the software stack and tell you
if the response is being misused. That is not going to happen.

For God sake! No, I am not! As soon as the correct data left the
PG-space in the format that the statement requested, and the KEY was
not NULL, of course, I do not blame PG.

Q4: If I used psql, I would get the correct data or not?


The query would run correctly, you would not be able to move data to 
Redis though.




You did ... I am
not seeing what the problem in the process is?

There is not any problem in the process at all. The process is
excellent. Now, I realise that our communication channels/frequencies
maybe are different. We have exchanged so many mails because we are
not able to understand each other.


...
It is not a joke ...

I do agree! That's why I said it's a joke! Ah, again different frequencies.


...
Except the problem you ran into:)

+ Eh, "you" added the new plan! Don't be unfair to the developer!


Not trying to be unfair, just pointing out things change and code needs 
to be tested against the changes.




...
There is also:

http://www.firebirdsql.org/
https://www.mysql.com/

There are more! Eh eh! "You" fooled me by writing that it is the most
advanced open source database in the world! I believed in "you" and
"your" words!


That is the projects motto, it has nothing to do with me:)


Mysql site writes "The world's most popular open source database". I
am not gonna get fooled again so easily!


I see motto's/slogans as marketing and I consider most marketing as a 
form of lying.




...
Which is exactly where you ran into a problem, so I question the easy
part. Still, go for it.

I do not get it! Different frequencies...


I was referring to this:

"But, when the application is finished, it will be very easy
to maintain the application-DB interface and use any other DB. It is a
matter of translation."

The problem you had was a matter of translation. Translation is always 
going to be tricky, especially the more translations you have to do in a 
project.



...
Pretty quickly from what I saw of their responses to your issues.

The enlightenment has not been quickly but easy easy we shall manage
to become together!



Have a nice evening!

Tia




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

Re: editable spreadsheet style interface

2018-10-30 Thread Martin Mueller

I have used Aqua Data Studio for several years. Jetbrains recently released a 
similar product. Academic licensing is affordable (~ $200 a year) and very 
cheap if considered in terms of the time it saves you.

From: David Gauthier 
Date: Tuesday, October 30, 2018 at 2:06 PM
To: "pgsql-gene...@postgresql.org" 
Subject: editable spreadsheet style interface

I think I know the answer to this one but I'll ask anyway...

Is there a spreadsheet style interface to a PG DB where users can...
- lock records
- edit records
- submit changes (transaction)

Is there any after-market tool for PG that does something like this ?

Re: Question about servicescript for stopping and starting postgresql instance

2018-10-30 Thread Marian Forums

Hi Adrain,
Thanks for your reply.
My script looks like this:
# cat /usr/lib/systemd/system/postgresql-9.6.pg-tstxxx.service
[Unit]
Description=postgresql_pg-tstxxx
After=syslog.target
After=network.target

[Service]
Type=forking
User=postgresql
Group=postgresqlg
Environment=PGDATA=/data/pg-tstxxx/data-01/
# Run ExecStartPre with root-permissions
PermissionsStartOnly=true
ExecStartPre=/bin/chown postgresql:postgresqlgroup /var/run/postgresql
ExecStart=/usr/pgsql-9.6/bin/pg_ctl start -D ${PGDATA}
ExecStop=/usr/pgsql-9.6/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.6/bin/pg_ctl reload -D ${PGDATA} -s
Restart=on-failure
KillMode=control-group
ExecStop=/bin/kill -SIGTERM $MAINPID
RestartSec=10s

[Install]
WantedBy=multi-user.target

thanks in advance.
regards,
Marian

Op di 30 okt. 2018 om 14:30 schreef Adrian Klaver :

> On 10/30/18 5:09 AM, Marian Forums wrote:
> > Hi,
> >
> > I have a question about creating a service script to start/stop/status
> > postgresql instance ( version 9.6.8) on Red Hat 7.
>
> How did you install Postgres on the machine?
>
> >
> > I have read the explanation of how to create such a service script on
> > https://www.postgresql.org/docs/9.6/static/server-start.html .
>
> Did you use the example at the above link or did you write your own?
>
> If you wrote your own can you show it?
>
>
> >
> > Stopping and Starting works.
> > However when I stop the service with:
> > systemctl stop postgresq.service and than ask for the status with
> > systemctl status postgresql.service, the service gets started again.
> >
> > Is this normal functionality on Red Hat 7?
> > I have searched on the Internet ( including the Red Hat site) for a
> > parameter to replace this behavior, but did not find anything.
> >
> > Your help is much appreciated.
> > Thanks in advance.
> > Regards,
> > Marian
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

editable spreadsheet style interface

2018-10-30 Thread David Gauthier

I think I know the answer to this one but I'll ask anyway...

Is there a spreadsheet style interface to a PG DB where users can...
- lock records
- edit records
- submit changes (transaction)

Is there any after-market tool for PG that does something like this ?

Cascading replication with slots

2018-10-30 Thread Scot Kreienkamp

Hi everyone,

I've never used replication slots before so I'm just wanting to clarify how 
they work on a two node streaming replication cluster.


1.  From the documentation both active and standby nodes seem to be aware 
of the status of the replication slot... is that true?

2.  Any limitations on using it with cascading replication?  For instance, 
can I setup a replication slot on a standby, then replicate using that slot 
from another standby?

3.  Or can I only replicate from the master when using replication slots?


Thanks!

Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162  | * 734-384-6403 | | * 7349151444 
| *  scot.kreienk...@la-z-boy.com
www.la-z-boy.com | 
facebook.com/lazboy
 | twitter.com/lazboy | 
youtube.com/lazboy

[cid:lzbVertical_hres.jpg]



This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: rw_redis_fdw: SQL Errors when statement is within a function

2018-10-30 Thread GPT

Good afternoon!
> ...
> Postgres did not behave in a 'poor' way, the extension just did not
> interpret the results correctly.
Eh! Eh! Adrian/Christoph one minute please because this is something
new (at least in the very clear way you formulate it now and I can
understand it easily)!
The statement was sent correctly from the module to PG; PG returned
the correct set of data to the module; BUT module failed to
interpret/present the data to me!

Q1: Is this what you are telling me?
  a) Yes
  b) No

Q2: Module sent the statement to the PG in a way A which PG does
understand. Did the PG sent back to the module in a way A or B or ...
which module understands?
  a) Yes
  b) No

Q3: And which part has "triggered" (so, is responsible for) the wrong
errors to appear on my screen,
  a) PG,
  b) the module,
  c) the java driver,
  d) PL/SQL,
  e) undefined,
  f) NULL or
  g) I do not know / I do not respond (joke!)?

>
> ... Postgres has no way of knowing what
> happens after its data is passed on.
In this case, I do not disagree at all!
>
> You are looking for Postgres to
> follow its responses all the way through the software stack and tell you
> if the response is being misused. That is not going to happen.
For God sake! No, I am not! As soon as the correct data left the
PG-space in the format that the statement requested, and the KEY was
not NULL, of course, I do not blame PG.

Q4: If I used psql, I would get the correct data or not?
>
> You did ... I am
> not seeing what the problem in the process is?
There is not any problem in the process at all. The process is
excellent. Now, I realise that our communication channels/frequencies
maybe are different. We have exchanged so many mails because we are
not able to understand each other.
>
>...
> It is not a joke ...
I do agree! That's why I said it's a joke! Ah, again different frequencies.
>
>...
> Except the problem you ran into:)
+ Eh, "you" added the new plan! Don't be unfair to the developer!
>
> ...
> There is also:
>
> http://www.firebirdsql.org/
> https://www.mysql.com/
There are more! Eh eh! "You" fooled me by writing that it is the most
advanced open source database in the world! I believed in "you" and
"your" words!
Mysql site writes "The world's most popular open source database". I
am not gonna get fooled again so easily!
>
> ...
> Which is exactly where you ran into a problem, so I question the easy
> part. Still, go for it.
I do not get it! Different frequencies...
> ...
> Pretty quickly from what I saw of their responses to your issues.
The enlightenment has not been quickly but easy easy we shall manage
to become together!
>
Have a nice evening!

Tia

Re: pg_stat_all_tables.last_vacuum not always correct.

2018-10-30 Thread Tom Lane

Kathleen Emerson  writes:
> I ran `VACUUM VERBOSE` on 10-28. After the VACUUM completed, I ran the
> query
> `SELECT relname, last_vacuum FROM pg_stat_all_tables ORDER BY 2;`
> [ and saw old timestamps for some ]
> So, at least according to the VERBOSE logs, it seems like these tables
> _were_ vacuumed. Why the discrepancy?

The pg_stats mechanism is designed to drop messages when under sufficient
load, so if there was a whole lot going on besides the VACUUM, maybe
that would explain it.

regards, tom lane

Re: Portworx snapshots

2018-10-30 Thread Stephen Frost

Greetings,

* Ghislain ROUVIGNAC (g...@sylob.com) wrote:
> Our application don't write lot of data, so i don't think the time taken on
> replaying the WAL will be an issue for us.

That certainly makes things simpler.

Then again, if you are not writing a lot of data then you might consider
using synchronous replication with PostgreSQL if you want to have a
durability guarantee which is across multiple otherwise independent
systems.  You can then also combine that with a proper backup solution
(please, do not try and build your own) and WAL archiving and be able to
perform PITR (point-in-time-recovery), which snapshots don't give you.

> For reliability, as you said, i was thinking in running a large pgbench
> which writes a lot of data, while taking snapshots.
> Then my idea was to restart from snapshots and see if everything works as
> expected.

Sure, testing is good and should be done regardless of what solution you
employ.

> I thought that based on the feedback from the community, maybe i wouldn't
> need to run these tests.

You should always run your own tests, and do them regularly, including
testing things like "am I able to restore this backup?", "am I able to
fail over to this other server?", etc.

Thanks!

Stephen

signature.asc
Description: PGP signature

Re: Portworx snapshots

2018-10-30 Thread Stephen Frost

Greetings,

* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> Stephen Frost wrote:
> > The downside with any snapshot-style approach is that it means that when
> > you have a failure, you have to go through and replay all the WAL since
> > the last checkpoint, which is single-threaded and can take a large
> > amount of time.
> > 
> > When doing your testing, I'd strongly recommend that you have a large
> > max_wal_size, run a large pgbench which writes a lot of data, and see
> > how long a failover takes with this system.
> 
> Then "checkpoint_timeout" should also be large, right?

Having a larger checkpoint timeout would also show that this method of
failover runs the risk of there being a very long time required between
when the failure is detected and when the new primary is online.

Thanks!

Stephen


signature.asc
Description: PGP signature

pg_stat_all_tables.last_vacuum not always correct.

2018-10-30 Thread Kathleen Emerson

I ran `VACUUM VERBOSE` on 10-28. After the VACUUM completed, I ran the
query

`SELECT relname, last_vacuum FROM pg_stat_all_tables ORDER BY 2;`

This query returned:

 relname |  last_vacuum
-+---
  | 2018-10-24 11:15:31.943684+00
 | 2018-10-24 11:15:31.963803+00
 | 2018-10-26 07:24:06.877427+00
 | 2018-10-26 07:24:06.884089+00
 | 2018-10-26 07:24:06.926874+00
 | 2018-10-26 07:24:06.927982+00
...

I took some of these tables and grep'd the VERBOSE logs for them, getting
results like:

INFO:  vacuuming ""
...
INFO:  "": found 0 removable, 198 nonremovable row versions in 28
out of 104513 pages

INFO:  vacuuming ""
INFO:  index "" now contains 1816 row versions in 7 pages
INFO:  "": found 0 removable, 6 nonremovable row versions in 1 out
of 325 pages

INFO:  vacuuming ""
...
INFO:  "": found 0 removable, 1 nonremovable row versions in 1 out
of 10924 pages

INFO:  vacuuming ""
...
INFO:  "": found 0 removable, 21 nonremovable row versions in 4 out
of 4 pages

INFO:  vacuuming ""
INFO:  "": found 0 removable, 2 nonremovable row versions in 1 out
of 412 pages

INFO:  vacuuming ""
...
INFO:  "": found 0 removable, 0 nonremovable row versions in 0 out
of 0 pages


So, at least according to the VERBOSE logs, it seems like these tables
_were_ vacuumed. Why the discrepancy?

Is the centos repository for postgresql 10 is broken now?

2018-10-30 Thread Олег Самойлов

In this form
https://www.postgresql.org/download/linux/redhat/

I set version 10, platform centos 7, arch x86_64 and I has got

yum install
https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

I did this. And on yum update I get:

[root@tuchanka1a yum.repos.d]# yum update
Загружены модули: fastestmirror
Loading mirror speeds from cached hostfile
Including mirror: mirror.yandex.ru
* base: mirror.yandex.ru
Including mirror: mirror.yandex.ru
* extras: mirror.yandex.ru
Including mirror: mirror.yandex.ru
* updates: mirror.yandex.ru
base| 3.6 kB 00:00
extras | 3.4 kB 00:00
pgdg10 | 4.1 kB 00:00
updates | 3.4 kB 00:00
zabbix | 2.9 kB 00:00
zabbix-non-supported| 951 B 00:00
pgdg10/7/x86_64/primary_db FAILED
http://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/repodata/d1a707282760737faba66b9507aca5d8616de42b883c7692ad09716ed9d5321c-primary.sqlite.bz2:
[Errno 14] HTTP Error 404 - Not Found
Пробуем другое зеркало.
To address this issue please refer to the below wiki article

https://wiki.centos.org/yum-errors

If above article doesn't help to resolve this issue please use
https://bugs.centos.org/.

pgdg10/7/x86_64/primary_db FAILED
http://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/repodata/d1a707282760737faba66b9507aca5d8616de42b883c7692ad09716ed9d5321c-primary.sqlite.bz2:
[Errno 14] HTTP Error 404 - Not Found
Пробуем другое зеркало.
http://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/repodata/d1a707282760737faba66b9507aca5d8616de42b883c7692ad09716ed9d5321c-primary.sqlite.bz2:
[Errno 14] HTTP Error 404 - Not Found
Пробуем другое зеркало.

One of the configured repositories failed (PostgreSQL 10 7 - x86_64),
and yum doesn't have enough cached data to continue. At this point the only
safe thing yum can do is fail. There are a few ways to work "fix" this:

1. Contact the upstream for the repository and get them to fix the problem.

2. Reconfigure the baseurl/etc. for the repository, to point to a working
upstream. This is most often useful if you are using a newer
distribution release than is supported by the repository (and the
packages for the previous distribution release still work).

3. Run the command with the repository temporarily disabled
yum --disablerepo=pgdg10 ...

4. Disable the repository permanently, so yum won't use it by default. Yum
will then just ignore the repository until you permanently enable it
again or use --enablerepo for temporary usage:

yum-config-manager --disable pgdg10
or
subscription-manager repos --disable=pgdg10

5. Configure the failing repository to be skipped, if it is unavailable.
Note that yum will try to contact the repo. when it runs most commands,
so will have to try and fail each time (and thus. yum will be be much
slower). If it is a very temporary problem though, this is often a nice
compromise:

yum-config-manager --save --setopt=pgdg10.skip_if_unavailable=true

failure:
repodata/d1a707282760737faba66b9507aca5d8616de42b883c7692ad09716ed9d5321c-primary.sqlite.bz2
from pgdg10: [Errno 256] No more mirrors to try.
http://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/repodata/d1a707282760737faba66b9507aca5d8616de42b883c7692ad09716ed9d5321c-primary.sqlite.bz2:
[Errno 14] HTTP Error 404 - Not Found
[root@tuchanka1a yum.repos.d]#

I checked, the file
http://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/repodata/d1a707282760737faba66b9507aca5d8616de42b883c7692ad09716ed9d5321c-primary.sqlite.bz2

indeed abscent, but exists
d4593f2aa6de4dcd80ca81de9e59429d00da390b6638d0f34de77cc3035bcc5e-primary.sqlite.bz2

Re: How to change standby node to sync from the new master without rebooting the PostgreSQL service?

2018-10-30 Thread Madan Kumar

It's not feasible since my deployments are in cloud.
We make use of floating IP for DB clients, but usage of same for within
cluster communication will be slight overhead.

Warm Regards,
Madan Kumar K 

*"There is no Elevator to Success. You have to take the Stairs"*


On Tue, Oct 30, 2018 at 7:57 PM Scot Kreienkamp <
scot.kreienk...@la-z-boy.com> wrote:

> Why is it not feasible?  How do your DB clients know to switch to the new
> master?
>
>
>
> I’m using pcs clustering in my environment to manage two production nodes,
> automatic failover, and two VIPs (one for master, one for slave).  All my
> clients point at either the master VIP or the slave VIP.  When we have a
> role change where the master is moved we do nothing to any clients or
> replication slaves, they automatically reconnect in all cases after the pcs
> cluster activates the two VIPs.  Also in case the primary slave would go
> down pcs will move the primary slave VIP to the master so that replication
> continues.  Makes maintenance easy, I can reboot the primary slave at any
> time and everything just continues working.
>
>
>
> There is easy to use open source software that will do nothing but VIPs.
> I used to use keepalived for that purpose.  You define a script that will
> let it determine which node to activate the VIP on.  In the script have it
> check which node is the master, and it will activate that VIP on the
> master.  When you transition the master to another server the VIP will
> travel with the master.
>
>
>
>
>
> *Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate*
> One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | |
> Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com
>
> *From:* Madan Kumar [mailto:madankumar1...@gmail.com]
> *Sent:* Tuesday, October 30, 2018 10:02 AM
> *To:* Scot Kreienkamp 
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: How to change standby node to sync from the new master
> without rebooting the PostgreSQL service?
>
>
>
> Thanks Scot.
>
> But moving VIP is not feasible option for me.
>
> At present PostgreSQL doesn't support for reloading of recovery.conf
> parameters via SIGHUP. To prevent recovery.conf reload for master IP, I can
> manage internal DNS to always point to the current master. However there
> are some cases where old master will come up as standby before the new
> master is elected. In this case it will lead to cascading replication.
>
>
>
> So to overcome such cases reboot is a required. It can be achieved by
> restarting the wal receiver process too. But there is no straight forward
> way of restarting wal receiver process. The only way i figured out is to
> kill the wal receiver process. Postmaster will take care of restarting the
> wal receiver  process. But here my worry is, will there be any side effect
> if i kill wal receiver process (even using TERM signal)?
>
>
>
> Warm Regards,
>
> Madan Kumar K 
>
>
>
> *"There is no Elevator to Success. You have to take the Stairs"*
>
>
>
>
>
> On Tue, Oct 30, 2018 at 6:27 PM Scot Kreienkamp <
> scot.kreienk...@la-z-boy.com> wrote:
>
> Point it at a VIP that travels with the master.
>
> *From:* Madan Kumar [mailto:madankumar1...@gmail.com]
> *Sent:* Tuesday, October 30, 2018 7:20 AM
> *To:*pgsql-general@lists.postgresql.org
> *Subject:* How to change standby node to sync from the new master without
> rebooting the PostgreSQL service?
>
>
>
> Hi,
>
>
>
> Whenever there is a change in master, PostgreSQL service on standby nodes
> must be restarted (after changing the master IP in the recovery.conf) to
> ensure it is syncing with the new master.
>
> Is there a way to point to new master without reboot of PostgreSQL on the
> standby?
>
>
>
> Warm Regards,
>
> Madan Kumar K 
>
>
>
> *"There is no Elevator to Success. You have to take the Stairs"*
>
> This message is intended only for the individual or entity to which it is
> addressed.  It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws.  If you are not the intended
> recipient, you are strictly prohibited from disseminating or distributing
> this information (other than to the intended recipient) or copying this
> information.  If you have received this communication in error, please
> notify us immediately by e-mail or by telephone at the above number. Thank
> you.
>
>

Re: R: Problem with stored procedure and nested transactions

2018-10-30 Thread Adrian Klaver


On 10/30/18 7:19 AM, p.piero...@mmbb.it wrote:

Thanks, I had already read it.
What I cannot find is how to get the behavior I described, is there a way?


Yes, follow the link to Section 43.8:

https://www.postgresql.org/docs/11/static/plpgsql-transactions.html




Paolo Pierotti


  
Viale Lombardia, 4 Lodi (LO)

M: +39 328 9035851
P: +39 075 8556435
W: www.mmbb.it

-Messaggio originale-
Da: Adrian Klaver 
Inviato: martedì 30 ottobre 2018 15:06
A: p.piero...@mmbb.it; pgsql-gene...@postgresql.org
Oggetto: Re: Problem with stored procedure and nested transactions

On 10/30/18 7:03 AM, p.piero...@mmbb.it wrote:

Hi everyone,

I have problems with stored procedures introduced in version 11.

I do not understand how to create a nested transaction, in this
semplified example:

*create**or**replace**procedure*tst_prc(*inout*p_cod *text*)
*language*plpgsql *as**$procedure$*

*begin*

p_cod := 'a';

*begin*

*update*aziende *set*mail = 'a...@asd.asd'*where*id = 11; --1st update

*begin*

*update*aziende *set*telefono = '0123456789'*where*id = 11; --2nd
update

*commit*;

*raise**notice*'Inner';

*end*;

*update*aziende *set*telefono = '089'*where*id = 11; --3rd update

--commit;

*rollback*;

*raise**notice*'Outer';

*end*;

p_cod := 'b';

*end*;

*$procedure$*

The third update goes, rightly, in rollback; the problem is that the
first 2 are committed.

I thought that the “BEGIN/END” block was used to create new
transactions and that each of them could be managed individually.


https://www.postgresql.org/docs/10/static/plpgsql-structure.html

"It is important not to confuse the use of BEGIN/END for grouping statements
in PL/pgSQL with the similarly-named SQL commands for transaction control.
PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a
transaction. See Section 43.8 for information on managing transactions in
PL/pgSQL. Also, a block containing an EXCEPTION clause effectively forms a
subtransaction that can be rolled back without affecting the outer
transaction. For more about that see "



What am I doing wrong?

Best regards.

**

*Paolo Pierotti *

**


http://www.mmbb.it/wp-content/uploads/2018/02/MMBB-logo.png

Viale Lombardia, 4 Lodi (LO)
M: +39 328 9035851

P: +39 075 8556435

W: www.mmbb.it 




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





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

RE: How to change standby node to sync from the new master without rebooting the PostgreSQL service?

2018-10-30 Thread Scot Kreienkamp

Why is it not feasible?  How do your DB clients know to switch to the new 
master?

I’m using pcs clustering in my environment to manage two production nodes, 
automatic failover, and two VIPs (one for master, one for slave).  All my 
clients point at either the master VIP or the slave VIP.  When we have a role 
change where the master is moved we do nothing to any clients or replication 
slaves, they automatically reconnect in all cases after the pcs cluster 
activates the two VIPs.  Also in case the primary slave would go down pcs will 
move the primary slave VIP to the master so that replication continues.  Makes 
maintenance easy, I can reboot the primary slave at any time and everything 
just continues working.

There is easy to use open source software that will do nothing but VIPs.  I 
used to use keepalived for that purpose.  You define a script that will let it 
determine which node to activate the VIP on.  In the script have it check which 
node is the master, and it will activate that VIP on the master.  When you 
transition the master to another server the VIP will travel with the master.



Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Madan Kumar [mailto:madankumar1...@gmail.com]
Sent: Tuesday, October 30, 2018 10:02 AM
To: Scot Kreienkamp 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: How to change standby node to sync from the new master without 
rebooting the PostgreSQL service?

Thanks Scot.
But moving VIP is not feasible option for me.
At present PostgreSQL doesn't support for reloading of recovery.conf parameters 
via SIGHUP. To prevent recovery.conf reload for master IP, I can manage 
internal DNS to always point to the current master. However there are some 
cases where old master will come up as standby before the new master is 
elected. In this case it will lead to cascading replication.

So to overcome such cases reboot is a required. It can be achieved by 
restarting the wal receiver process too. But there is no straight forward way 
of restarting wal receiver process. The only way i figured out is to kill the 
wal receiver process. Postmaster will take care of restarting the wal receiver  
process. But here my worry is, will there be any side effect if i kill wal 
receiver process (even using TERM signal)?

Warm Regards,
Madan Kumar K

"There is no Elevator to Success. You have to take the Stairs"


On Tue, Oct 30, 2018 at 6:27 PM Scot Kreienkamp 
mailto:scot.kreienk...@la-z-boy.com>> wrote:
Point it at a VIP that travels with the master.
From: Madan Kumar 
[mailto:madankumar1...@gmail.com]
Sent: Tuesday, October 30, 2018 7:20 AM
To:pgsql-general@lists.postgresql.org
Subject: How to change standby node to sync from the new master without 
rebooting the PostgreSQL service?

Hi,

Whenever there is a change in master, PostgreSQL service on standby nodes must 
be restarted (after changing the master IP in the recovery.conf) to ensure it 
is syncing with the new master.
Is there a way to point to new master without reboot of PostgreSQL on the 
standby?

Warm Regards,
Madan Kumar K

"There is no Elevator to Success. You have to take the Stairs"

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Re: rw_redis_fdw: SQL Errors when statement is within a function

2018-10-30 Thread Adrian Klaver


On 10/30/18 3:19 AM, GPT wrote:

Very good morning!

If, in 2018 when the human structures are very close to reach the edge
of our universe, here on earth you are asking me (a simple end-user),
in order to run the following "complicated" and "sophisticated" SQL
statements:

INSERT INTO my_table(key, value, expiry) VALUES ('my_key', 'my_value', 10);
SELECT value FROM my_table where key = LOWER('my_key');

ten (10) times in total, to activate the debugger and check the log
files because the errors given to me were **totally wrong** ("KEY is
NULL" was not NULL) and **misleading**, then one more time: It´s
TRAGIC!

I, of course, recognise that I have made plenty mistakes! But, I could
never imagine that the most advanced open source database in the world
would behave in such a "poor" way!


Postgres did not behave in a 'poor' way, the extension just did not 
interpret the results correctly. This gets to the crux of the problem, 
third party code using Postgres. Postgres has no way of knowing what 
happens after its data is passed on. You are looking for Postgres to 
follow its responses all the way through the software stack and tell you 
if the response is being misused. That is not going to happen. You did 
the correct thing, report the issue to the extension author and post to 
this list for further advice. As a result the issue was corrected. I am 
not seeing what the problem in the process is?




@Christoph

As regards "rw_redis_fdw" I have to admit the following:
- it is honest. It informs the user from the very beginning that "This
project is currently work in progress and may have experience
significant changes until it becomes stable. Use it with caution and
at your own risk!"; although it is so far stable enough!!!
- it worked in pg9.x without problem!
- the developer did his best when I reported the problem; for my bad
luck he did not expirienced the same problem in PG10.x so as he starts
searching/debugging from the very first moment.
- after **including and NOT fixing** (because there was not any bug)
the FuncExpr subquery support, the module has been working fine and it
is stable! (until of course the next plan change that PG will induce
under the hood and brakes the interface; that´s a joke!) although the


It is not a joke it is a fact of life and the reason for major version 
changes. They are allowed to make breaking changes in the code. It is 
how software progresses. It is also why there is a long testing period, 
alpha-->beta-->rc, to give users and third party developers plenty of 
opportunity to try their code against the changes.



developer uses (successfully - I add) very low level, internal
interfaces to PostgreSQL as you have already written.

And the most important:

The module **DOES** whatever claims to do without a problem, in a
very humble way!


Except the problem you ran into:)



As regards the risk of the project, I am aware of it. That´s why I am
trying to choose **reliable**, **responsible** and **trustworthy**
projects, even if they are not famous! From a very famous project, I
just recently got a burn! Do only **ONE** thing but do it
**"CORRECTLY"**! I always try to avoid rich-featured projects which,
by rule, most of the features are mis-implemented, or quality is poor!

As regards database choice, I do not have many options, PG or MariaDB
or SQLite. But, when the application is finished, it will be very easy


There is also:

http://www.firebirdsql.org/
https://www.mysql.com/


to maintain the application-DB interface and use any other DB. It is a
matter of translation. DB is just a tool like any other one, and not a


Which is exactly where you ran into a problem, so I question the easy 
part. Still, go for it.



religous matter.

In the case of redis fdw I do not have many options. There are
two-three of them. Redis is the only db which offers TTL with very
high resolution (1 sec), and rw_redis_fdw implements TTL.

In addition, at first opportunity, I always contact the developer or
the community to get an idea of his/its mentality and the way they
work or react or keep their nose up.


Pretty quickly from what I saw of their responses to your issues.



Have all a nice day!

Tia



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

R: Problem with stored procedure and nested transactions

2018-10-30 Thread p.pierotti

Thanks, I had already read it.
What I cannot find is how to get the behavior I described, is there a way?


Paolo Pierotti 


 
Viale Lombardia, 4 Lodi (LO) 
M: +39 328 9035851 
P: +39 075 8556435
W: www.mmbb.it 

-Messaggio originale-
Da: Adrian Klaver  
Inviato: martedì 30 ottobre 2018 15:06
A: p.piero...@mmbb.it; pgsql-gene...@postgresql.org
Oggetto: Re: Problem with stored procedure and nested transactions

On 10/30/18 7:03 AM, p.piero...@mmbb.it wrote:
> Hi everyone,
> 
> I have problems with stored procedures introduced in version 11.
> 
> I do not understand how to create a nested transaction, in this 
> semplified example:
> 
> *create**or**replace**procedure*tst_prc(*inout*p_cod *text*) 
> *language*plpgsql *as**$procedure$*
> 
> *begin*
> 
> p_cod := 'a';
> 
> *begin*
> 
> *update*aziende *set*mail = 'a...@asd.asd'*where*id = 11; --1st update
> 
> *begin*
> 
> *update*aziende *set*telefono = '0123456789'*where*id = 11; --2nd 
> update
> 
> *commit*;
> 
> *raise**notice*'Inner';
> 
> *end*;
> 
> *update*aziende *set*telefono = '089'*where*id = 11; --3rd update
> 
> --commit;
> 
> *rollback*;
> 
> *raise**notice*'Outer';
> 
> *end*;
> 
> p_cod := 'b';
> 
> *end*;
> 
> *$procedure$*
> 
> The third update goes, rightly, in rollback; the problem is that the 
> first 2 are committed.
> 
> I thought that the BEGIN/END block was used to create new 
> transactions and that each of them could be managed individually.

https://www.postgresql.org/docs/10/static/plpgsql-structure.html

"It is important not to confuse the use of BEGIN/END for grouping statements
in PL/pgSQL with the similarly-named SQL commands for transaction control.
PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a
transaction. See Section 43.8 for information on managing transactions in
PL/pgSQL. Also, a block containing an EXCEPTION clause effectively forms a
subtransaction that can be rolled back without affecting the outer
transaction. For more about that see "

> 
> What am I doing wrong?
> 
> Best regards.
> 
> **
> 
> *Paolo Pierotti *
> 
> **
> 
> 
> http://www.mmbb.it/wp-content/uploads/2018/02/MMBB-logo.png
> 
> Viale Lombardia, 4 Lodi (LO)
> M: +39 328 9035851
> 
> P: +39 075 8556435
> 
> W: www.mmbb.it 
> 


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

Re: Problem with stored procedure and nested transactions

2018-10-30 Thread Adrian Klaver


On 10/30/18 7:03 AM, p.piero...@mmbb.it wrote:

Hi everyone,

I have problems with stored procedures introduced in version 11.

I do not understand how to create a nested transaction, in this 
semplified example:


*create**or**replace**procedure*tst_prc(*inout*p_cod *text*) 
*language*plpgsql *as**$procedure$*


*begin*

p_cod := 'a';

*begin*

*update*aziende *set*mail = 'a...@asd.asd'*where*id = 11; --1st update

*begin*

*update*aziende *set*telefono = '0123456789'*where*id = 11; --2nd update

*commit*;

*raise**notice*'Inner';

*end*;

*update*aziende *set*telefono = '089'*where*id = 11; --3rd update

--commit;

*rollback*;

*raise**notice*'Outer';

*end*;

p_cod := 'b';

*end*;

*$procedure$*

The third update goes, rightly, in rollback; the problem is that the 
first 2 are committed.


I thought that the “BEGIN/END” block was used to create new transactions 
and that each of them could be managed individually.


https://www.postgresql.org/docs/10/static/plpgsql-structure.html

"It is important not to confuse the use of BEGIN/END for grouping 
statements in PL/pgSQL with the similarly-named SQL commands for 
transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do 
not start or end a transaction. See Section 43.8 for information on 
managing transactions in PL/pgSQL. Also, a block containing an EXCEPTION 
clause effectively forms a subtransaction that can be rolled back 
without affecting the outer transaction. For more about that see "




What am I doing wrong?

Best regards.

**

*Paolo Pierotti *

**


http://www.mmbb.it/wp-content/uploads/2018/02/MMBB-logo.png

Viale Lombardia, 4 Lodi (LO)
M: +39 328 9035851

P: +39 075 8556435

W: www.mmbb.it 




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

Problem with stored procedure and nested transactions

2018-10-30 Thread p.pierotti

Hi everyone,

I have problems with stored procedures introduced in version 11.

I do not understand how to create a nested transaction, in this semplified
example:

 

create or replace procedure tst_prc(inout p_cod text) language plpgsql as
$procedure$

begin

p_cod := 'a';

   begin

 update aziende set mail = 'a...@asd.asd' where id = 11; --1st
update

 begin

update aziende set telefono = '0123456789' where id =
11; --2nd update

commit;

raise notice 'Inner';

 end;

 update aziende set telefono = '089' where id = 11; --3rd update

 --commit;

 rollback;

 raise notice 'Outer';

   end;

p_cod := 'b';

end;

 

$procedure$

 

 

The third update goes, rightly, in rollback; the problem is that the first 2
are committed.

 

I thought that the "BEGIN/END" block was used to create new transactions and
that each of them could be managed individually.

 

What am I doing wrong?

 

Best regards.

 

 

Paolo Pierotti 

 


 

Viale Lombardia, 4 Lodi (LO) 
M: +39 328 9035851 

P: +39 075 8556435

W:   www.mmbb.it

Re: How to change standby node to sync from the new master without rebooting the PostgreSQL service?

2018-10-30 Thread Madan Kumar

Thanks Scot.
But moving VIP is not feasible option for me.
At present PostgreSQL doesn't support for reloading of recovery.conf
parameters via SIGHUP. To prevent recovery.conf reload for master IP, I can
manage internal DNS to always point to the current master. However there
are some cases where old master will come up as standby before the new
master is elected. In this case it will lead to cascading replication.

So to overcome such cases reboot is a required. It can be achieved by
restarting the wal receiver process too. But there is no straight forward
way of restarting wal receiver process. The only way i figured out is to
kill the wal receiver process. Postmaster will take care of restarting the
wal receiver  process. But here my worry is, will there be any side effect
if i kill wal receiver process (even using TERM signal)?

Warm Regards,
Madan Kumar K 

*"There is no Elevator to Success. You have to take the Stairs"*

On Tue, Oct 30, 2018 at 6:27 PM Scot Kreienkamp <
scot.kreienk...@la-z-boy.com> wrote:

> Point it at a VIP that travels with the master.
>
>
>
> *Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate*
> One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | |
> Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com
>
> *From:* Madan Kumar [mailto:madankumar1...@gmail.com]
> *Sent:* Tuesday, October 30, 2018 7:20 AM
> *To:* pgsql-general@lists.postgresql.org
> *Subject:* How to change standby node to sync from the new master without
> rebooting the PostgreSQL service?
>
>
>
> Hi,
>
>
>
> Whenever there is a change in master, PostgreSQL service on standby nodes
> must be restarted (after changing the master IP in the recovery.conf) to
> ensure it is syncing with the new master.
>
> Is there a way to point to new master without reboot of PostgreSQL on the
> standby?
>
>
>
> Warm Regards,
>
> Madan Kumar K 
>
>
>
> *"There is no Elevator to Success. You have to take the Stairs"*
>
> This message is intended only for the individual or entity to which it is
> addressed.  It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws.  If you are not the intended
> recipient, you are strictly prohibited from disseminating or distributing
> this information (other than to the intended recipient) or copying this
> information.  If you have received this communication in error, please
> notify us immediately by e-mail or by telephone at the above number.
> Thank you.
>

Re: Question about servicescript for stopping and starting postgresql instance

2018-10-30 Thread Adrian Klaver


On 10/30/18 5:09 AM, Marian Forums wrote:

Hi,

I have a question about creating a service script to start/stop/status 
postgresql instance ( version 9.6.8) on Red Hat 7.


How did you install Postgres on the machine?



I have read the explanation of how to create such a service script on 
https://www.postgresql.org/docs/9.6/static/server-start.html .


Did you use the example at the above link or did you write your own?

If you wrote your own can you show it?




Stopping and Starting works.
However when I stop the service with:
systemctl stop postgresq.service and than ask for the status with 
systemctl status postgresql.service, the service gets started again.


Is this normal functionality on Red Hat 7?
I have searched on the Internet ( including the Red Hat site) for a 
parameter to replace this behavior, but did not find anything.


Your help is much appreciated.
Thanks in advance.
Regards,
Marian



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

RE: How to change standby node to sync from the new master without rebooting the PostgreSQL service?

2018-10-30 Thread Scot Kreienkamp

Point it at a VIP that travels with the master.

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Madan Kumar [mailto:madankumar1...@gmail.com]
Sent: Tuesday, October 30, 2018 7:20 AM
To: pgsql-general@lists.postgresql.org
Subject: How to change standby node to sync from the new master without 
rebooting the PostgreSQL service?

Hi,

Whenever there is a change in master, PostgreSQL service on standby nodes must 
be restarted (after changing the master IP in the recovery.conf) to ensure it 
is syncing with the new master.
Is there a way to point to new master without reboot of PostgreSQL on the 
standby?

Warm Regards,
Madan Kumar K

"There is no Elevator to Success. You have to take the Stairs"

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

Question about servicescript for stopping and starting postgresql instance

2018-10-30 Thread Marian Forums

Hi,

I have a question about creating a service script to start/stop/status
postgresql instance ( version 9.6.8) on Red Hat 7.

I have read the explanation of how to create such a service script on
https://www.postgresql.org/docs/9.6/static/server-start.html .

Stopping and Starting works.
However when I stop the service with:
systemctl stop postgresq.service and than ask for the status with systemctl
status postgresql.service, the service gets started again.

Is this normal functionality on Red Hat 7?
I have searched on the Internet ( including the Red Hat site) for a
parameter to replace this behavior, but did not find anything.

Your help is much appreciated.
Thanks in advance.
Regards,
Marian

Re: Problem with commit in function

2018-10-30 Thread Christoph Moench-Tegeder

## Mike Martin (redt...@gmail.com):

> Subject: Problem with commit in function

You can't commit inside a FUNCTION - and there's an obvious ERROR if
you try to do that: "invalid transaction termination".
Only since version 11 you can use a PROCEDURE and COMMIT/ROLLBACK
inside that - and the procedure must be written in PL/pgSQL (you
don't get transaction control in SQL procedures - but beside the
LANGUAGE marker, your code would be the same).
See
  https://www.postgresql.org/docs/current/static/sql-createprocedure.html
  https://www.postgresql.org/docs/current/static/plpgsql-transactions.html

> truncate table postgres_log_tmp  ;

You might want to look into temporary tables (perhaps even unlogged
ones) - that will save you the hassle of truncating (temporary tables
are even automatically removed), and with an unlogged temp table it
will save you some WAL I/O.

> --COMMIT;

So, is this on or not?
Oh, and please pay attention to the errors PostgreSQL throws at
you - they're significant.

Regards,
Christoph

-- 
Spare Space

How to change standby node to sync from the new master without rebooting the PostgreSQL service?

2018-10-30 Thread Madan Kumar

Hi,

Whenever there is a change in master, PostgreSQL service on standby nodes
must be restarted (after changing the master IP in the recovery.conf) to
ensure it is syncing with the new master.
Is there a way to point to new master without reboot of PostgreSQL on the
standby?

Warm Regards,
Madan Kumar K 

*"There is no Elevator to Success. You have to take the Stairs"*

Problem with commit in function

2018-10-30 Thread Mike Martin

I have the following function
-- FUNCTION: public.update_log()

-- DROP FUNCTION public.update_log();

CREATE OR REPLACE FUNCTION public.update_log(
)
RETURNS void
LANGUAGE 'sql'

COST 100
VOLATILE
AS $BODY$

truncate table postgres_log_tmp  ;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Mon.csv' WITH
csv;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Tue.csv' WITH
csv;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Wed.csv' WITH
csv;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Thu.csv' WITH
csv;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Fri.csv' WITH
csv;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Sat.csv' WITH
csv;
COPY postgres_log_tmp FROM '/mnt/pgdata/data/log/postgresql-Sun.csv' WITH
csv;
INSERT INTO postgres_log SELECT * from postgres_log_tmp ON
CONFLICT(session_id, session_line_num) DO NOTHING;
--COMMIT;
truncate table postgres_log_tmp  ;

$BODY$;

ALTER FUNCTION public.update_log()
OWNER TO postgres;

If I leave the second truncate statement nothing is written to
postgres_log. I assume the insert doesnt finish

Any way to force it to finish before the truncation?

Re: rw_redis_fdw: SQL Errors when statement is within a function

2018-10-30 Thread GPT

Very good morning!

If, in 2018 when the human structures are very close to reach the edge
of our universe, here on earth you are asking me (a simple end-user),
in order to run the following "complicated" and "sophisticated" SQL
statements:

INSERT INTO my_table(key, value, expiry) VALUES ('my_key', 'my_value', 10);
SELECT value FROM my_table where key = LOWER('my_key');

ten (10) times in total, to activate the debugger and check the log
files because the errors given to me were **totally wrong** ("KEY is
NULL" was not NULL) and **misleading**, then one more time: It´s
TRAGIC!

I, of course, recognise that I have made plenty mistakes! But, I could
never imagine that the most advanced open source database in the world
would behave in such a "poor" way!

@Christoph

As regards "rw_redis_fdw" I have to admit the following:
- it is honest. It informs the user from the very beginning that "This
project is currently work in progress and may have experience
significant changes until it becomes stable. Use it with caution and
at your own risk!"; although it is so far stable enough!!!
- it worked in pg9.x without problem!
- the developer did his best when I reported the problem; for my bad
luck he did not expirienced the same problem in PG10.x so as he starts
searching/debugging from the very first moment.
- after **including and NOT fixing** (because there was not any bug)
the FuncExpr subquery support, the module has been working fine and it
is stable! (until of course the next plan change that PG will induce
under the hood and brakes the interface; that´s a joke!) although the
developer uses (successfully - I add) very low level, internal
interfaces to PostgreSQL as you have already written.

And the most important:

The module **DOES** whatever claims to do without a problem, in a
very humble way!

As regards the risk of the project, I am aware of it. That´s why I am
trying to choose **reliable**, **responsible** and **trustworthy**
projects, even if they are not famous! From a very famous project, I
just recently got a burn! Do only **ONE** thing but do it
**"CORRECTLY"**! I always try to avoid rich-featured projects which,
by rule, most of the features are mis-implemented, or quality is poor!

As regards database choice, I do not have many options, PG or MariaDB
or SQLite. But, when the application is finished, it will be very easy
to maintain the application-DB interface and use any other DB. It is a
matter of translation. DB is just a tool like any other one, and not a
religous matter.

In the case of redis fdw I do not have many options. There are
two-three of them. Redis is the only db which offers TTL with very
high resolution (1 sec), and rw_redis_fdw implements TTL.

In addition, at first opportunity, I always contact the developer or
the community to get an idea of his/its mentality and the way they
work or react or keep their nose up.

Have all a nice day!

Tia

http://www.avg.com/email-signature?utm_medium=email_source=link_campaign=sig-email_content=webmail;
target="_blank">https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png;
alt="" width="46" height="29" style="width: 46px; height: 29px;"
/>
Libre de virus. http://www.avg.com/email-signature?utm_medium=email_source=link_campaign=sig-email_content=webmail;
target="_blank" style="color: #4453ea;">www.avg.com

Connectivity Support for PostgreSQL 11 in dbForge Data Compare for PostgreSQL

2018-10-30 Thread Devart

Devart has released a new version of dbForge Data Compare for PostgreSQL
v.3.1 – a powerful and easy to use tool for table data comparison and
synchronization.

Devart, a Czech software provider of database connectivity solutions and
tools for database management and development, announced the release of
PostgreSQL data diff management tool, dbForge Data Compare for PostgreSQL
v3.1, that comes with support for PostgreSQL 10.x and 11.x.
The new version provides connectivity support for the latest versions of
PostgreSQL which means users can compare, analyze, and deploy PostgreSQL
data diffs on the most up-to-date database engines.

dbForge Data Compare for PostgreSQL is a PostgreSQL Data Diff and Sync tool
that allows users to review all the differences in tables being compared and
execute an automatically generated script to eliminate these differences.

For more information about dbForge Data Compare for PostgreSQL, please visit
https://www.devart.com/dbforge/postgresql/datacompare/

About Devart
Devart is one of the leading developers of database tools and administration
software, ALM solutions, data providers for various database servers, data
integration and backup solutions. The company also implements Web and Mobile
development projects.
Learn more about Devart at https://www.devart.com.





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Different memory allocation strategy in Postgres 11?

2018-10-30 Thread Thomas Kellerer

Thomas Munro schrieb am 26.10.2018 um 22:13:
>>> I typically configure "shared_buffers = 4096MB" on my 16GB system as 
>>> sometimes when testing, it pays off to have a bigger cache.
>>>
>>> With Postgres 10 and earlier, the Postgres process(es) would only 
>>> allocate that memory from the operating system when needed.
>>> So right after startup, it would only consume several hundred MB, not 
>>> the entire 4GB
>>>
>>> However with Postgres 11 I noticed that it immediately grabs the 
>>> complete memory configured for shared_buffers during startup.
>>>
>>> It's not really a big deal, but I wonder if that is an intentional 
>>> change or a result from something else?
>>>
>>>
>>> Do you have pg_prewarm in shared_preload_libraries?
>>
>> No. The only shared libraries are those for pg_stat_statemens
> 
> Does your user have "Lock Pages in Memory" privilege?  One thing that
> is new in 11 is huge AKA large page support, and the default is
> huge_pages=try.  Not a Windows person myself but I believe that should
> succeed if you have that privilege and enough contiguous chunks of
> physical memory are available.  If you set huge_pages=off does it
> revert to the old behaviour?

Turns out this was an "optimization" in Windows 10, and completely unrelated to 
Postgres.

Windows 10 has a feature called "Fast Boot" (or something along the lines). 

When that is activated (which it is by default), a proper shutdown of the 
system does not seem to really shut it down. This is especially noteworthy with 
services: they don't get a shutdown event (which e.g. means even a service 
marked as "manual start", will still be running after a reboot if it did 
before) 

In case of Postgres this is visible e.g. in the logfile, because there will no 
shutdown or startup messages. 

So when I booted my laptop, Postgres continued where it was before the reboot - 
and the memory usage was caused caused by myself generating test data using 
generate_series() but I expected a "clean" state after the reboot.

When manually restarting the service everything works as expected. 

Sorry for the noise.

38 matches

Mail list logo