Re: pgbackrest restore to new location?

[Luca Ferrari]

On Tue, Sep 17, 2019 at 3:09 AM Ron  wrote:
>
> Hi,
>
> In order to do this, do I create a new stanza in config file which has
> pg1-path point to the new/empty directory structure while still pointing to
> the existing backup directory, and restore that stanza?

No, I would do this:
1) execute stop for that stanza on the backup machine
2) change the pg1-path for that stanza on the target machine
3) execute the restore command on the target machine
4) adjust backup machine stanza path and 'start' it again.

If you create a new stanza, pgbackrest will not find it in the backup
repository.

Luca

Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

[stan]

On Mon, Sep 16, 2019 at 03:19:27PM -0700, Adrian Klaver wrote:
> On 9/16/19 12:55 PM, stan wrote:
> > 
> > On Mon, Sep 16, 2019 at 12:44:49PM -0700, Adrian Klaver wrote:
> > > On 9/16/19 11:53 AM, stan wrote:
> > > > On Sun, Sep 15, 2019 at 09:16:35PM -0700, Adrian Klaver wrote:
> > > > > On 9/15/19 6:04 PM, stan wrote:
> > > > > > On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:
> > > > > > > On 9/15/19 10:46 AM, stan wrote:
> > > > > > > > Forgot to cc the list again. Have to look at settings in mutt.
> > > > > > > > 
> > > > > > > > > > > 
> > > > > 
> > > > > > > What validity check?
> > > > > > > 
> > > > > > 
> > > > > > The check to see if it is the type enum.
> > > > > > 
> > > > > 
> > > > > This would get solved a lot quicker if full information was provided:
> > > > > 
> > > > > 1) Schema of the table.
> > > > >   Including associated triggers
> > > > > 
> > > > > 2) The actual check code.
> > > > > 
> > > > 
> > > > OK, please let me know if what I put in my reply to Tom Lane is not 
> > > > sufficient.
> > > > 
> > > 
> > > 
> > > It was not sufficient, you did not include the table schema or the check
> > > code.
> > 
> > OK, understood here is the table:
> > 
> > /* Contains one record for each customer */
> > 
> 
> >  status activity_status NOT NULL DEFAULT 'ACTIVE',
> >  modtimetimestamptz NOT NULL DEFAULT current_timestamp
> > );
> > 
> > I am not certain what you mean by the check. As you can see, there is nor
> > specific check clause. I was referring to the built in check of data being
> > entered versus the legal values for the user defined type. Make sense?
> > 
> 
> To be clear you are seeing an error because a value of say 'active' is being
> rejected before your trigger has a chance to upper case it, correct?
> 
> Also this happens whether you use \copy or manually INSERT the values?

That is correct. Sorry this was not clear from the beginning.

Any suggestions, including changing the design here, are welcome.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: pgbackrest restore to new location?

[Ron]

On 9/17/19 4:29 AM, Luca Ferrari wrote:

On Tue, Sep 17, 2019 at 3:09 AM Ron  wrote:

Hi,

In order to do this, do I create a new stanza in config file which has
pg1-path point to the new/empty directory structure while still pointing to
the existing backup directory, and restore that stanza?

No, I would do this:
1) execute stop for that stanza on the backup machine
2) change the pg1-path for that stanza on the target machine
3) execute the restore command on the target machine
4) adjust backup machine stanza path and 'start' it again.

If you create a new stanza, pgbackrest will not find it in the backup
repository.


Thanks, Luca.  I realized that mistake when trying the restore.

The real problem is that after doing that, "pg_ctl start -D 
/path/to/new/data" fails with "PANIC: could not locate a valid checkpoint 
record".


--
Angular momentum makes the world go 'round.

Re: pgbackrest restore to new location?

[Luca Ferrari]

On Tue, Sep 17, 2019 at 12:00 PM Ron  wrote:
> The real problem is that after doing that, "pg_ctl start -D
> /path/to/new/data" fails with "PANIC: could not locate a valid checkpoint
> record".

Hard to say what's going wrong without logs/configs. Do you have any
other backup to try to restore from? As a sidenote, in this kind of
restore you should not be using --delta, but I guess pgbackrest will
prevent you to do so.

Luca

Re: pgbackrest restore to new location?

[David Steele]

On 9/17/19 7:23 AM, Luca Ferrari wrote:
> On Tue, Sep 17, 2019 at 12:00 PM Ron  wrote:
>> The real problem is that after doing that, "pg_ctl start -D
>> /path/to/new/data" fails with "PANIC: could not locate a valid checkpoint
>> record".

Sounds like backup_label is missing or has been deleted.

The easiest way to restore to a new location is just to copy
pgbackrest.conf from the primary (or create a new one) with the same
stanza and then alter pg1-path in pgbackrest.conf or at the command line.

Regards,

-- 
-David
da...@pgmasters.net

Re: PostgreSQL License

[Ashkar Dev]

Thank you all,
but it is saying (without fee)
if I create a database with it to work with Web Application if want to sell
it so the buyer must have the PostgreSQL installed in his device to work
offline right?
"Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, *without fee*, and without a written
agreement is hereby granted, provided that the above copyright notice and
this paragraph and the following two paragraphs appear in all copies."

>
On Mon, Sep 16, 2019 at 11:18 PM Laurenz Albe 
wrote:

> Please don't cross-post your question to more than one list!
>
> Ashkar Dev wrote:
> > Can anyone explain the PostgreSQL License to me?
> > Can I create a database with it and sell the database also preventing
> > the buyer from reselling it?
> > Can I change in the logos of the PostgreSQL system and its name?
>
> Yes.
>
> You only have to make sure that the original license text is included
> in your license.  This does not limit what you are allowed to do with
> the software.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

max_connections parameter: too_many_connections error

[Shital A]

*Hello*,

We are working on a payments systems which uses postgreSql 9.6 as backend
DB and blockchain technology. The database is setup in HA in master-standby
mode using pacemaker on Linux 7.6.

*We are new to postgres and ne*ed help in deciding how to set value for
max_connections on DB.

1. How can we decide on optimal value for max_connections for a given
setup/server? I checked many posts saying that even 1000 is considered as a
very high value but we are hitting the error too_many_connections due to
Max_connections value limit.

2. We usee hikari pool on the client side but that even has limitations.
Implementing pg_bouncer is another suggestion in the posts. In a HA setup,
do we have to set it up on both primary standby?

Please provide your advice/suggestions.

Thanks.

RE: max_connections parameter: too_many_connections error

[Scot Kreienkamp]

1. How can we decide on optimal value for max_connections for a given 
setup/server? I checked many posts saying that even 1000 is considered as a 
very high value but we are hitting the error too_many_connections due to 
Max_connections value limit.



I have one set at 1000 but I usually top out around 500 right now.  As long as 
you have the CPU and memory to handle whatever you’ve set I would think it’s 
ok.  Hopefully others with more experience with big hardware will chime in.



2. We usee hikari pool on the client side but that even has limitations. 
Implementing pg_bouncer is another suggestion in the posts. In a HA setup, do 
we have to set it up on both primary standby?



Client side pooling is a different proposition than server side.  Each server 
maintaining its own pool VS the server maintaining a common pool, the server 
side pooling would lower your connection count.  The value of pooling also 
depends on your app.  If it’s maintaining persistent connections for hours at a 
time then any kind of pooling is going to be of limited value depending on how 
the pooler is operating, IE connection pooling VS transactional pooling.If 
your transactions are simple you could use transactional pooling which could 
greatly reduce the connection count.



For an HA setup you would need to set it up on both PG nodes.  If you’re using 
the standby for read-only queries then it would have to be running on both 
nodes at all times and pacemaker would start/stop it as part of the failover 
process.  If you’re only using the active node then pacemaker would have to 
start the pooler on the active node and stop it on the passive node, and again 
control it on both nodes for failover.  If it’s that large of a setup you may 
want to make the pooler its own cluster with pacemaker to relieve the PG 
cluster of the additional load.

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:4C-lzbVertical_9ddbc47c-2ac7-4ab5-9162-d7bc17d5d136.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: FW: Re: FW: Re: Shouldn;t this trigger be called?

[Adrian Klaver]

On 9/17/19 2:31 AM, stan wrote:


I am not certain what you mean by the check. As you can see, there is nor
specific check clause. I was referring to the built in check of data being
entered versus the legal values for the user defined type. Make sense?



To be clear you are seeing an error because a value of say 'active' is being
rejected before your trigger has a chance to upper case it, correct?

Also this happens whether you use \copy or manually INSERT the values?


That is correct. Sorry this was not clear from the beginning.

Any suggestions, including changing the design here, are welcome.


Suggestions:

1) Per Tom's post clean the data before it hits the database.

2) Enter the data directly into the database using something that forces 
the user to enter only the correct ENUM values.


3) If the status field is only ever going to be ACTIVE/INACTIVE change 
it to a BOOLEAN field active_status and be done with the ENUM dance.


4) If status may ever be more then ACTIVE/ACTIVE then change it to 
varchar and use the trigger to set case(if still important) and/or 
verify correct entries.



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

Re: pldbgapi extension

[Adrian Klaver]

On 9/16/19 11:51 PM, Prakash Ramakrishnan wrote:

Hi Team,

Not able to create the extension pldbgapi in cluster and if i run make 
and make install it will going .sql file in /usr/share/pgsql/extension 
this path but actually i need to create these files into below path,


How to create this one please advise


Questions:

1) OS and version?

2) How was Postgres installed?

3) How about symlinking /usr/share/pgsql/extension into 
/usr/pgsql-11/share/extension/?





==> psql
psql (11.5)
Type "help" for help.

postgres=# CREATE EXTENSION pldbgapi;
ERROR:  could not open extension control file 
*"/usr/pgsql-11/share/extension/*pldbgapi.control": No such file or 
directory

postgres=#
postgres=#
postgres=# \q

[root@cvgrhehhsd006 pldebugger-master]# export 
PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin:$PGBASE/edbmtk/bin:/usr/pgsql-11/bin

[root@cvgrhehhsd006 pldebugger-master]#  make USE_PGXS=1
make: Nothing to be done for `all'.
[root@cvgrhehhsd006 pldebugger-master]#
[root@cvgrhehhsd006 pldebugger-master]# make install USE_PGXS=1
/usr/bin/mkdir -p '/usr/lib64/pgsql'
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/mkdir -p '/usr/share/doc/pgsql/extension'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c 
-m 755  plugin_debugger.so '/usr/lib64/pgsql/plugin_debugger.so'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c 
-m 644 ./pldbgapi.control '/usr/share/pgsql/extension/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c 
-m 644 ./pldbgapi--1.0.sql ./pldbgapi--unpackaged--1.0.sql 
  '/usr/share/pgsql/extension/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c 
-m 644 ./README.pldebugger '/usr/share/doc/pgsql/extension/'

[root@cvgrhehhsd006 pldebugger-master]#
[root@cvgrhehhsd006 pldebugger-master]# cd /usr/share/pgsql/extension
[root@cvgrhehhsd006 extension]# ls
pldbgapi--1.0.sql  pldbgapi.control  pldbgapi--unpackaged--1.0.sql
[root@cvgrhehhsd006 extension]# ll
total 16
-rw-r--r--. 1 root root 7457 Sep 17 02:46 pldbgapi--1.0.sql
-rw-r--r--. 1 root root  181 Sep 17 02:46 pldbgapi.control
-rw-r--r--. 1 root root 2258 Sep 17 02:46 pldbgapi--unpackaged--1.0.sql

--
Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On 
call : +91-8939599426



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

Re: pldbgapi extension

[Tom Lane]

Prakash Ramakrishnan  writes:
> Not able to create the extension  pldbgapi in cluster and if i run make and
> make install it will going .sql file in  /usr/share/pgsql/extension this
> path but actually i need to create these files into below path,

If you're building from source with intention to match an existing
installation, you need to be sure you've configured with the same
options the existing installation was built with.  The output of
the installation's "pg_config" program should help.

When building an out-of-tree extension using PGXS, in theory the
PGXS infrastructure should get this right for you, but maybe
it isn't, or you missed out using it.

regards, tom lane

Re: pgbackrest restore to new location?

[Ron]

On 9/17/19 6:48 AM, David Steele wrote:

On 9/17/19 7:23 AM, Luca Ferrari wrote:

On Tue, Sep 17, 2019 at 12:00 PM Ron  wrote:

The real problem is that after doing that, "pg_ctl start -D
/path/to/new/data" fails with "PANIC: could not locate a valid checkpoint
record".

Sounds like backup_label is missing or has been deleted.

The easiest way to restore to a new location is just to copy
pgbackrest.conf from the primary (or create a new one) with the same
stanza and then alter pg1-path in pgbackrest.conf or at the command line.


That's what I did.  (Also, I opened Issue #839 in GitHub  All of the log 
files are attached there.)



--
Angular momentum makes the world go 'round.

Re: max_connections parameter: too_many_connections error

[Rui DeSousa]

> On Sep 17, 2019, at 8:43 AM, Shital A  wrote:
> 
> Hello,
> 
> We are working on a payments systems which uses postgreSql 9.6 as backend DB 
> and blockchain technology. The database is setup in HA in master-standby mode 
> using pacemaker on Linux 7.6.
> 
> We are new to postgres and need help in deciding how to set value for 
> max_connections on DB.
> 
> 1. How can we decide on optimal value for max_connections for a given 
> setup/server? I checked many posts saying that even 1000 is considered as a 
> very high value but we are hitting the error too_many_connections due to 
> Max_connections value limit.
> 
I’d say 2x the number you expect to have connect to the database as you can not 
change the value without bouncing the service and downtime is usually hard to 
come by.  Just set the monitoring solution at 50%.   That’s what I currently 
do; using a max connection of 2000 with an average connection rate of around 
800.  That sounds like a lot but with 96 cores that’s only around 9 connections 
per core.  
> 2. We usee hikari pool on the client side but that even has limitations. 
> Implementing pg_bouncer is another suggestion in the posts. In a HA setup, do 
> we have to set it up on both primary standby?
> 

Take a look at pg_bouncer.   The problem with a high number of connections is 
that the idle connections will wake up from poll() and spin on the cpu.  I’m in 
the process of testing pg_bouncer to reduce the number PostgreSQL connections 
to resolve the high server load due to idle sessions.  pg_bouncer has proven to 
be effective as it doesn’t suffer from the idle poll() issue and it has reduced 
the server load due to idle sessions. 

I have been wondering if PostgreSQL used kqueues instead of poll if it would 
allow the server to scale better without having to introduce pg_bouncer.

Re: pldbgapi extension

[Prakash Ramakrishnan]

Hi Adrian,

I did it yum installation postgresql 11 from repo file.but I don't know why
it's giving wrong path and symbolic link how to create it please advise.


Regards,
Prakash.R

On Tue, Sep 17, 2019, 19:10 Adrian Klaver  wrote:

> On 9/16/19 11:51 PM, Prakash Ramakrishnan wrote:
> > Hi Team,
> >
> > Not able to create the extension pldbgapi in cluster and if i run make
> > and make install it will going .sql file in /usr/share/pgsql/extension
> > this path but actually i need to create these files into below path,
> >
> > How to create this one please advise
>
> Questions:
>
> 1) OS and version?
>
> 2) How was Postgres installed?
>
> 3) How about symlinking /usr/share/pgsql/extension into
> /usr/pgsql-11/share/extension/?
>
>
> >
> > ==> psql
> > psql (11.5)
> > Type "help" for help.
> >
> > postgres=# CREATE EXTENSION pldbgapi;
> > ERROR:  could not open extension control file
> > *"/usr/pgsql-11/share/extension/*pldbgapi.control": No such file or
> > directory
> > postgres=#
> > postgres=#
> > postgres=# \q
> >
> > [root@cvgrhehhsd006 pldebugger-master]# export
> >
> PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin:$PGBASE/edbmtk/bin:/usr/pgsql-11/bin
> > [root@cvgrhehhsd006 pldebugger-master]#  make USE_PGXS=1
> > make: Nothing to be done for `all'.
> > [root@cvgrhehhsd006 pldebugger-master]#
> > [root@cvgrhehhsd006 pldebugger-master]# make install USE_PGXS=1
> > /usr/bin/mkdir -p '/usr/lib64/pgsql'
> > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
> > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
> > /usr/bin/mkdir -p '/usr/share/doc/pgsql/extension'
> > /bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> > -m 755  plugin_debugger.so '/usr/lib64/pgsql/plugin_debugger.so'
> > /bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> > -m 644 ./pldbgapi.control '/usr/share/pgsql/extension/'
> > /bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> > -m 644 ./pldbgapi--1.0.sql ./pldbgapi--unpackaged--1.0.sql
> >   '/usr/share/pgsql/extension/'
> > /bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> > -m 644 ./README.pldebugger '/usr/share/doc/pgsql/extension/'
> > [root@cvgrhehhsd006 pldebugger-master]#
> > [root@cvgrhehhsd006 pldebugger-master]# cd /usr/share/pgsql/extension
> > [root@cvgrhehhsd006 extension]# ls
> > pldbgapi--1.0.sql  pldbgapi.control  pldbgapi--unpackaged--1.0.sql
> > [root@cvgrhehhsd006 extension]# ll
> > total 16
> > -rw-r--r--. 1 root root 7457 Sep 17 02:46 pldbgapi--1.0.sql
> > -rw-r--r--. 1 root root  181 Sep 17 02:46 pldbgapi.control
> > -rw-r--r--. 1 root root 2258 Sep 17 02:46 pldbgapi--unpackaged--1.0.sql
> >
> > --
> > Thanks,
> > Prakash.R
> > PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
> > call : +91-8939599426
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: pldbgapi extension

[Adrian Klaver]

On 9/17/19 7:20 AM, Prakash Ramakrishnan wrote:

Hi Adrian,

I did it yum installation postgresql 11 from repo file.but I don't know 
why it's giving wrong path and symbolic link how to create it please advise.


Looks like it is installing to source built extension directory, not the 
package one.


You are using the PGDG repos?

Did you install postgresql11-devel from the repo?

I am not that familiar with the RH based multi-version install so I am 
not sure that -devel will help or not, but it is worth a try.





Regards,
Prakash.R

On Tue, Sep 17, 2019, 19:10 Adrian Klaver > wrote:


On 9/16/19 11:51 PM, Prakash Ramakrishnan wrote:
 > Hi Team,
 >
 > Not able to create the extension pldbgapi in cluster and if i run
make
 > and make install it will going .sql file in
/usr/share/pgsql/extension
 > this path but actually i need to create these files into below path,
 >
 > How to create this one please advise

Questions:

1) OS and version?

2) How was Postgres installed?

3) How about symlinking /usr/share/pgsql/extension into
/usr/pgsql-11/share/extension/?


 >
 > ==> psql
 > psql (11.5)
 > Type "help" for help.
 >
 > postgres=# CREATE EXTENSION pldbgapi;
 > ERROR:  could not open extension control file
 > *"/usr/pgsql-11/share/extension/*pldbgapi.control": No such file or
 > directory
 > postgres=#
 > postgres=#
 > postgres=# \q
 >
 > [root@cvgrhehhsd006 pldebugger-master]# export
 >

PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin:$PGBASE/edbmtk/bin:/usr/pgsql-11/bin
 > [root@cvgrhehhsd006 pldebugger-master]#  make USE_PGXS=1
 > make: Nothing to be done for `all'.
 > [root@cvgrhehhsd006 pldebugger-master]#
 > [root@cvgrhehhsd006 pldebugger-master]# make install USE_PGXS=1
 > /usr/bin/mkdir -p '/usr/lib64/pgsql'
 > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
 > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
 > /usr/bin/mkdir -p '/usr/share/doc/pgsql/extension'
 > /bin/sh
/usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 > -m 755  plugin_debugger.so '/usr/lib64/pgsql/plugin_debugger.so'
 > /bin/sh
/usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 > -m 644 ./pldbgapi.control '/usr/share/pgsql/extension/'
 > /bin/sh
/usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 > -m 644 ./pldbgapi--1.0.sql ./pldbgapi--unpackaged--1.0.sql
 >   '/usr/share/pgsql/extension/'
 > /bin/sh
/usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 > -m 644 ./README.pldebugger '/usr/share/doc/pgsql/extension/'
 > [root@cvgrhehhsd006 pldebugger-master]#
 > [root@cvgrhehhsd006 pldebugger-master]# cd /usr/share/pgsql/extension
 > [root@cvgrhehhsd006 extension]# ls
 > pldbgapi--1.0.sql  pldbgapi.control  pldbgapi--unpackaged--1.0.sql
 > [root@cvgrhehhsd006 extension]# ll
 > total 16
 > -rw-r--r--. 1 root root 7457 Sep 17 02:46 pldbgapi--1.0.sql
 > -rw-r--r--. 1 root root  181 Sep 17 02:46 pldbgapi.control
 > -rw-r--r--. 1 root root 2258 Sep 17 02:46
pldbgapi--unpackaged--1.0.sql
 >
 > --
 > Thanks,
 > Prakash.R
 > PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure
Team On
 > call : +91-8939599426


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: pldbgapi extension

[Prakash Ramakrishnan]

I installed devel and contrib also but not worked here.


Regards,
Prakash.R

On Tue, Sep 17, 2019, 19:58 Adrian Klaver  wrote:

> On 9/17/19 7:20 AM, Prakash Ramakrishnan wrote:
> > Hi Adrian,
> >
> > I did it yum installation postgresql 11 from repo file.but I don't know
> > why it's giving wrong path and symbolic link how to create it please
> advise.
>
> Looks like it is installing to source built extension directory, not the
> package one.
>
> You are using the PGDG repos?
>
> Did you install postgresql11-devel from the repo?
>
> I am not that familiar with the RH based multi-version install so I am
> not sure that -devel will help or not, but it is worth a try.
>
> >
> >
> > Regards,
> > Prakash.R
> >
> > On Tue, Sep 17, 2019, 19:10 Adrian Klaver  > > wrote:
> >
> > On 9/16/19 11:51 PM, Prakash Ramakrishnan wrote:
> >  > Hi Team,
> >  >
> >  > Not able to create the extension pldbgapi in cluster and if i run
> > make
> >  > and make install it will going .sql file in
> > /usr/share/pgsql/extension
> >  > this path but actually i need to create these files into below
> path,
> >  >
> >  > How to create this one please advise
> >
> > Questions:
> >
> > 1) OS and version?
> >
> > 2) How was Postgres installed?
> >
> > 3) How about symlinking /usr/share/pgsql/extension into
> > /usr/pgsql-11/share/extension/?
> >
> >
> >  >
> >  > ==> psql
> >  > psql (11.5)
> >  > Type "help" for help.
> >  >
> >  > postgres=# CREATE EXTENSION pldbgapi;
> >  > ERROR:  could not open extension control file
> >  > *"/usr/pgsql-11/share/extension/*pldbgapi.control": No such file
> or
> >  > directory
> >  > postgres=#
> >  > postgres=#
> >  > postgres=# \q
> >  >
> >  > [root@cvgrhehhsd006 pldebugger-master]# export
> >  >
> >
>  
> PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin:$PGBASE/edbmtk/bin:/usr/pgsql-11/bin
> >  > [root@cvgrhehhsd006 pldebugger-master]#  make USE_PGXS=1
> >  > make: Nothing to be done for `all'.
> >  > [root@cvgrhehhsd006 pldebugger-master]#
> >  > [root@cvgrhehhsd006 pldebugger-master]# make install USE_PGXS=1
> >  > /usr/bin/mkdir -p '/usr/lib64/pgsql'
> >  > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
> >  > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
> >  > /usr/bin/mkdir -p '/usr/share/doc/pgsql/extension'
> >  > /bin/sh
> > /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> >  > -m 755  plugin_debugger.so '/usr/lib64/pgsql/plugin_debugger.so'
> >  > /bin/sh
> > /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> >  > -m 644 ./pldbgapi.control '/usr/share/pgsql/extension/'
> >  > /bin/sh
> > /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> >  > -m 644 ./pldbgapi--1.0.sql ./pldbgapi--unpackaged--1.0.sql
> >  >   '/usr/share/pgsql/extension/'
> >  > /bin/sh
> > /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
> >  > -m 644 ./README.pldebugger '/usr/share/doc/pgsql/extension/'
> >  > [root@cvgrhehhsd006 pldebugger-master]#
> >  > [root@cvgrhehhsd006 pldebugger-master]# cd
> /usr/share/pgsql/extension
> >  > [root@cvgrhehhsd006 extension]# ls
> >  > pldbgapi--1.0.sql  pldbgapi.control  pldbgapi--unpackaged--1.0.sql
> >  > [root@cvgrhehhsd006 extension]# ll
> >  > total 16
> >  > -rw-r--r--. 1 root root 7457 Sep 17 02:46 pldbgapi--1.0.sql
> >  > -rw-r--r--. 1 root root  181 Sep 17 02:46 pldbgapi.control
> >  > -rw-r--r--. 1 root root 2258 Sep 17 02:46
> > pldbgapi--unpackaged--1.0.sql
> >  >
> >  > --
> >  > Thanks,
> >  > Prakash.R
> >  > PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure
> > Team On
> >  > call : +91-8939599426
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: deadlock on declarative partitioned table (11.3)

[Kevin Wilkinson]

disregard. problem found. i was locking the wrong table.

On 9/16/2019 11:10 AM, Kevin Wilkinson wrote:
on linux, pg11.3, i have a (declarative) partitioned table with a 
deadlock that i do not understand. one process does a copy to the 
partitioned table. another process is executing a jdbc batch of 
commands to "atomically" replace one of the table partitions. it has 
the following commands (autocommit is off).


   lock table foo;
   alter table foo detach partition foo_nn;
   alter table foo_nn rename to foo_nn_old;
   alter table new_foo_nn rename to foo_nn
   alter table foo attach partition foo_nn for values  from (...) to 
(...);

   commit;

the log says the deadlock is on the first alter table command but i 
think that is misleading. i suspect what is happening is that the 
explicit lock command attempts to lock each partition of foo in turn 
rather than locking all partitions immediately. so it acquires some 
locks in some unknown order while the copy acquires locks as needed. 
so they deadlock.


or is something else going on? is there a better way to atomically 
replace a table partition? the table is partitioned by timestamp but i 
don't think that matters.


thanks,

kevin

Re: pldbgapi extension

[Adrian Klaver]

On 9/17/19 7:44 AM, Prakash Ramakrishnan wrote:

I installed devel and contrib also but not worked here.


Yeah, --contrib won't help as pldbpgapi is not one of the contrib 
modules as found here:


https://www.postgresql.org/docs/11/contrib.html

The basic issue is you are using two install methods YUM for your 
Postgres server and source build for the pldbpgapi extension. They are 
using different directory structures. I think the best bet for the time 
being is to use the symklink suggestion I posted earlier. Someone with 
more experience with RH may also chime in with a better solution.





Regards,
Prakash.R

On Tue, Sep 17, 2019, 19:58 Adrian Klaver > wrote:


On 9/17/19 7:20 AM, Prakash Ramakrishnan wrote:
 > Hi Adrian,
 >
 > I did it yum installation postgresql 11 from repo file.but I
don't know
 > why it's giving wrong path and symbolic link how to create it
please advise.

Looks like it is installing to source built extension directory, not
the
package one.

You are using the PGDG repos?

Did you install postgresql11-devel from the repo?

I am not that familiar with the RH based multi-version install so I am
not sure that -devel will help or not, but it is worth a try.

 >
 >
 > Regards,
 > Prakash.R
 >
 > On Tue, Sep 17, 2019, 19:10 Adrian Klaver
mailto:adrian.kla...@aklaver.com>
 > >> wrote:
 >
 >     On 9/16/19 11:51 PM, Prakash Ramakrishnan wrote:
 >      > Hi Team,
 >      >
 >      > Not able to create the extension pldbgapi in cluster and
if i run
 >     make
 >      > and make install it will going .sql file in
 >     /usr/share/pgsql/extension
 >      > this path but actually i need to create these files into
below path,
 >      >
 >      > How to create this one please advise
 >
 >     Questions:
 >
 >     1) OS and version?
 >
 >     2) How was Postgres installed?
 >
 >     3) How about symlinking /usr/share/pgsql/extension into
 >     /usr/pgsql-11/share/extension/?
 >
 >
 >      >
 >      > ==> psql
 >      > psql (11.5)
 >      > Type "help" for help.
 >      >
 >      > postgres=# CREATE EXTENSION pldbgapi;
 >      > ERROR:  could not open extension control file
 >      > *"/usr/pgsql-11/share/extension/*pldbgapi.control": No
such file or
 >      > directory
 >      > postgres=#
 >      > postgres=#
 >      > postgres=# \q
 >      >
 >      > [root@cvgrhehhsd006 pldebugger-master]# export
 >      >
 >   
  PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin:$PGBASE/edbmtk/bin:/usr/pgsql-11/bin

 >      > [root@cvgrhehhsd006 pldebugger-master]#  make USE_PGXS=1
 >      > make: Nothing to be done for `all'.
 >      > [root@cvgrhehhsd006 pldebugger-master]#
 >      > [root@cvgrhehhsd006 pldebugger-master]# make install
USE_PGXS=1
 >      > /usr/bin/mkdir -p '/usr/lib64/pgsql'
 >      > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
 >      > /usr/bin/mkdir -p '/usr/share/pgsql/extension'
 >      > /usr/bin/mkdir -p '/usr/share/doc/pgsql/extension'
 >      > /bin/sh
 >     /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 >      > -m 755  plugin_debugger.so
'/usr/lib64/pgsql/plugin_debugger.so'
 >      > /bin/sh
 >     /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 >      > -m 644 ./pldbgapi.control '/usr/share/pgsql/extension/'
 >      > /bin/sh
 >     /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 >      > -m 644 ./pldbgapi--1.0.sql ./pldbgapi--unpackaged--1.0.sql
 >      >   '/usr/share/pgsql/extension/'
 >      > /bin/sh
 >     /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c
 >      > -m 644 ./README.pldebugger '/usr/share/doc/pgsql/extension/'
 >      > [root@cvgrhehhsd006 pldebugger-master]#
 >      > [root@cvgrhehhsd006 pldebugger-master]# cd
/usr/share/pgsql/extension
 >      > [root@cvgrhehhsd006 extension]# ls
 >      > pldbgapi--1.0.sql  pldbgapi.control
  pldbgapi--unpackaged--1.0.sql
 >      > [root@cvgrhehhsd006 extension]# ll
 >      > total 16
 >      > -rw-r--r--. 1 root root 7457 Sep 17 02:46 pldbgapi--1.0.sql
 >      > -rw-r--r--. 1 root root  181 Sep 17 02:46 pldbgapi.control
 >      > -rw-r--r--. 1 root root 2258 Sep 17 02:46
 >     pldbgapi--unpackaged--1.0.sql
 >      >
 >      > --
 >      > Thanks,
 >      > Prakash.R
 >      > PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure
 >     Team On
 >      > call : +91-8939599426
 >
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 

Re: pldbgapi extension

[Rob Sargent]

On 9/17/19 10:16 AM, Adrian Klaver wrote:

On 9/17/19 7:44 AM, Prakash Ramakrishnan wrote:

I installed devel and contrib also but not worked here.


Yeah, --contrib won't help as pldbpgapi is not one of the contrib 
modules as found here:


https://www.postgresql.org/docs/11/contrib.html

The basic issue is you are using two install methods YUM for your 
Postgres server and source build for the pldbpgapi extension. They are 
using different directory structures. I think the best bet for the 
time being is to use the symklink suggestion I posted earlier. Someone 
with more experience with RH may also chime in with a better solution.



If OP is building the extension, why not build postgres too?

Re: pldbgapi extension

[Adrian Klaver]

On 9/17/19 9:20 AM, Rob Sargent wrote:


On 9/17/19 10:16 AM, Adrian Klaver wrote:

On 9/17/19 7:44 AM, Prakash Ramakrishnan wrote:

I installed devel and contrib also but not worked here.


Yeah, --contrib won't help as pldbpgapi is not one of the contrib 
modules as found here:


https://www.postgresql.org/docs/11/contrib.html

The basic issue is you are using two install methods YUM for your 
Postgres server and source build for the pldbpgapi extension. They are 
using different directory structures. I think the best bet for the 
time being is to use the symklink suggestion I posted earlier. Someone 
with more experience with RH may also chime in with a better solution.



If OP is building the extension, why not build postgres too?



Because installing from packages is easier?

Looks like the module is available in the repo under a different name:

https://yum.postgresql.org/testing/11/redhat/rhel-7-x86_64/repoview/pldebugger11.html


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

Re: pldbgapi extension

[sivareddy umma]

Yes debugging module is available in repo, installation from yum is very
easy

Regards,
Siva.

On Tue, Sep 17, 2019, 10:50 PM Adrian Klaver  On 9/17/19 9:20 AM, Rob Sargent wrote:
> >
> > On 9/17/19 10:16 AM, Adrian Klaver wrote:
> >> On 9/17/19 7:44 AM, Prakash Ramakrishnan wrote:
> >>> I installed devel and contrib also but not worked here.
> >>
> >> Yeah, --contrib won't help as pldbpgapi is not one of the contrib
> >> modules as found here:
> >>
> >> https://www.postgresql.org/docs/11/contrib.html
> >>
> >> The basic issue is you are using two install methods YUM for your
> >> Postgres server and source build for the pldbpgapi extension. They are
> >> using different directory structures. I think the best bet for the
> >> time being is to use the symklink suggestion I posted earlier. Someone
> >> with more experience with RH may also chime in with a better solution.
> >>
> > If OP is building the extension, why not build postgres too?
> >
>
> Because installing from packages is easier?
>
> Looks like the module is available in the repo under a different name:
>
>
> https://yum.postgresql.org/testing/11/redhat/rhel-7-x86_64/repoview/pldebugger11.html
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

pg_rewind: invalid record length

[Ben Wheatley]

Hi,

We're encountering an issue when running pg_rewind, and are looking for advice
on how to proceed.

We have a set of 3 Postgres instances which are being restored from the same
physical disk snapshot (which was taken from a standby on a production system),
in order to test a disaster recovery setup.

The cluster management software that we're using picks one of these 3 instances
as a primary, promotes it, and then 'resyncs' the other two instances in order
to boot them as standbys. A resync uses pg_rewind, because it does not know the
provenance of the existing data on disk, and attempts to bring the two
instances into line in case the timelines have diverged.

In this configuration our target timeline is a direct ancestor of the source
timeline, rather than a fork.


When pg_rewind is run under this configuration, we encounter the following
error (debug output included):

---
connected to server
fetched file "global/pg_control", length 8192
fetched file "pg_wal/000E.history", length 561
Source timeline history:
Target timeline history:
1: 0/0 - 0/7144130
2: 0/7144130 - 0/10806F98
3: 0/10806F98 - 0/1198
4: 0/1198 - 0/1298
5: 0/1298 - 0/1398
6: 0/1398 - 0/1498
7: 0/1498 - 0/14000288
8: 0/14000288 - 0/1598
9: 0/1598 - 0/16000C88
10: 0/16000C88 - 0/1898
11: 0/1898 - 0/180001A8
12: 0/180001A8 - 0/190736B0
13: 0/190736B0 - 0/0
servers diverged at WAL location 0/2223F588 on timeline 13

could not find previous WAL record at 0/2223F588: invalid record
length at 0/2223F588: wanted 24, got 0
Failure, exiting
---

We believe this to mean that pg_rewind, when attempting to determine the last
checkpoint, has started the search at the end of the WAL that exists on disk,
in the position where the next expected WAL record was going to be written, and
therefore returns an error because it can't examine the xl_prev field of the
XLogRecord, in order to continue the search backwards for the checkpoint.

The questions that we're faced with are:
1. Is this a valid use of pg_rewind? (i.e. rewinding when there has been no
fork)
2. What is causing pg_rewind to begin its search at the end of the WAL? (is
this because we haven't actually written any data in this cluster since the
promotion)
3. Would it be valid and/or correct to make pg_rewind skip backwards if it
finds a zero-length record? (this does seem to mitigate the error - see
attached patch)

Thanks,
Ben


0001-Fix-pg_rewind-when-divergence-is-at-end-of-WAL.patch
Description: Binary data

install pgcrypto module to existing postgreSQL

[Pavan Kumar]

hello experts,

i have Installed and created postgres 11.2 and created couple of database.
Now i got new requirement to encrypt data on  few tables. to  perform this
application team is asking us to install pgcrypto module on postgres
cluster.
what is the way to install pgcrypt library ?

please advise

-- 



*Regards,#!  Pavan Kumar--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell#  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

Re: PostgreSQL License

[Peter J. Holzer]

On 2019-09-17 14:56:30 +0300, Ashkar Dev wrote:
> but it is saying (without fee)
> if I create a database with it to work with Web Application if want to sell it
> so the buyer must have the PostgreSQL installed in his device to work offline
> right?
> "Permission to use, copy, modify, and distribute this software and its
> documentation for any purpose, without fee, and without a written agreement is
> hereby granted, provided that the above copyright notice and this paragraph 
> and
> the following two paragraphs appear in all copies."

This means that you don't have to pay a fee or sign a written agreement
to use, copy, modify, and distribute this software and its documentation
for any purpose. It doesn't say that you can't charge a fee for
distributing (although why anybody would pay you for something they can
download themselves for free I don't know).

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: install pgcrypto module to existing postgreSQL

[Adrian Klaver]

On 9/17/19 1:25 PM, Pavan Kumar wrote:

hello experts,

i have Installed and created postgres 11.2 and created couple of 


How did you install?

database.  Now i got new requirement to encrypt data on  few tables. to  
perform this application team is asking us to install pgcrypto module on 
postgres cluster.

what is the way to install pgcrypt library ?

please advise

--
*Regards,

#!  Pavan Kumar
--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you
will be Wise *




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

Re: install pgcrypto module to existing postgreSQL

[Pavan Kumar]

Hello Adrian,

i have used configure command install postgres

  $ ./configure --prefix=/pgbin/11.2 --with-segsize=32 --with-pgport=5432

once configure is done, used make and make install to install postgres.



On Tue, Sep 17, 2019 at 4:06 PM Adrian Klaver 
wrote:

> On 9/17/19 1:25 PM, Pavan Kumar wrote:
> > hello experts,
> >
> > i have Installed and created postgres 11.2 and created couple of
>
> How did you install?
>
> > database.  Now i got new requirement to encrypt data on  few tables. to
> > perform this application team is asking us to install pgcrypto module on
> > postgres cluster.
> > what is the way to install pgcrypt library ?
> >
> > please advise
> >
> > --
> > *Regards,
> >
> > #!  Pavan Kumar
> > --*-
> > *Sr. Database Administrator..!*
> > *NEXT GENERATION PROFESSIONALS, LLC*
> > *Cell#  267-799-3182 #  pavan.dba27 (Gtalk) *
> > *India   # 9000459083*
> >
> > *Take Risks; if you win, you will be very happy. If you lose you
> > will be Wise *
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 



*Regards,#!  Pavan Kumar--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell#  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

Re: install pgcrypto module to existing postgreSQL

[Adrian Klaver]

On 9/17/19 2:18 PM, Pavan Kumar wrote:

Hello Adrian,

i have used configure command install postgres

   $ ./configure --prefix=/pgbin/11.2 --with-segsize=32 --with-pgport=5432

once configure is done, used make and make install to install postgres.


Full instructions are here at bottom:
https://www.postgresql.org/docs/11/contrib.html





On Tue, Sep 17, 2019 at 4:06 PM Adrian Klaver > wrote:


On 9/17/19 1:25 PM, Pavan Kumar wrote:
 > hello experts,
 >
 > i have Installed and created postgres 11.2 and created couple of

How did you install?

 > database.  Now i got new requirement to encrypt data on  few
tables. to
 > perform this application team is asking us to install pgcrypto
module on
 > postgres cluster.
 > what is the way to install pgcrypt library ?
 >
 > please advise
 >
 > --
 > *Regards,
 >
 > #!  Pavan Kumar
 > --*-
 > *Sr. Database Administrator..!*
 > *NEXT GENERATION PROFESSIONALS, LLC*
 > *Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
 > *India   # 9000459083*
 >
 >     *Take Risks; if you win, you will be very happy. If you lose you
 >     will be Wise *
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
*Regards,

#!  Pavan Kumar
--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you
will be Wise *




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

Re: install pgcrypto module to existing postgreSQL

[Ron]

IOW, you installed it from source instead of a package.

On 9/17/19 4:18 PM, Pavan Kumar wrote:

Hello Adrian,

i have used configure command install postgres

  $ ./configure --prefix=/pgbin/11.2 --with-segsize=32 --with-pgport=5432

once configure is done, used make and make install to install postgres.



On Tue, Sep 17, 2019 at 4:06 PM Adrian Klaver > wrote:


On 9/17/19 1:25 PM, Pavan Kumar wrote:
> hello experts,
>
> i have Installed and created postgres 11.2 and created couple of

How did you install?

> database.  Now i got new requirement to encrypt data on few tables. to
> perform this application team is asking us to install pgcrypto
module on
> postgres cluster.
> what is the way to install pgcrypt library ?
>
> please advise



--
Angular momentum makes the world go 'round.

Re: install pgcrypto module to existing postgreSQL

[Pavan Kumar]

Ron,

yes, Installed with source code. here is my postgres configuration
BINDIR = /pgbin/11.2/bin
DOCDIR = /pgbin/11.2/share/doc/postgresql
HTMLDIR = /pgbin/11.2/share/doc/postgresql
INCLUDEDIR = /pgbin/11.2/include
PKGINCLUDEDIR = /pgbin/11.2/include/postgresql
INCLUDEDIR-SERVER = /pgbin/11.2/include/postgresql/server
LIBDIR = /pgbin/11.2/lib
PKGLIBDIR = /pgbin/11.2/lib/postgresql
LOCALEDIR = /pgbin/11.2/share/locale
MANDIR = /pgbin/11.2/share/man
SHAREDIR = /pgbin/11.2/share/postgresql
SYSCONFDIR = /pgbin/11.2/etc/postgresql
PGXS = /pgbin/11.2/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/pgbin/11.2' '--with-segsize=32' '--with-pgport=5432'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/pgbin/11.2/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 11.2

On Tue, Sep 17, 2019 at 4:22 PM Ron  wrote:

> IOW, you installed it from source instead of a package.
>
> On 9/17/19 4:18 PM, Pavan Kumar wrote:
>
> Hello Adrian,
>
> i have used configure command install postgres
>
>   $ ./configure --prefix=/pgbin/11.2 --with-segsize=32 --with-pgport=5432
>
> once configure is done, used make and make install to install postgres.
>
>
>
> On Tue, Sep 17, 2019 at 4:06 PM Adrian Klaver 
> wrote:
>
>> On 9/17/19 1:25 PM, Pavan Kumar wrote:
>> > hello experts,
>> >
>> > i have Installed and created postgres 11.2 and created couple of
>>
>> How did you install?
>>
>> > database.  Now i got new requirement to encrypt data on  few tables.
>> to
>> > perform this application team is asking us to install pgcrypto module
>> on
>> > postgres cluster.
>> > what is the way to install pgcrypt library ?
>> >
>> > please advise
>>
>
> --
> Angular momentum makes the world go 'round.
>


-- 



*Regards,#!  Pavan Kumar--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell#  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

Re: PostgreSQL License

[Andreas Joseph Krogh]

På tirsdag 17. september 2019 kl. 22:55:02, skrev Peter J. Holzer <
hjp-pg...@hjp.at >: On 2019-09-17 14:56:30 +0300, 
Ashkar Dev wrote:
 > but it is saying (without fee)
 > if I create a database with it to work with Web Application if want to sell 
it
 > so the buyer must have the PostgreSQL installed in his device to work 
offline
 > right?
 > "Permission to use, copy, modify, and distribute this software and its
 > documentation for any purpose, without fee, and without a written agreement 
is
 > hereby granted, provided that the above copyright notice and this paragraph 
and
 > the following two paragraphs appear in all copies."

 This means that you don't have to pay a fee or sign a written agreement
 to use, copy, modify, and distribute this software and its documentation
 for any purpose. It doesn't say that you can't charge a fee for
 distributing (although why anybody would pay you for something they can
 download themselves for free I don't know).

 hp A rule of thumb is - you can do anything you want with it (the PG software 
inc. its source), except claim you wrote it, as long as you preserve the 
original license-file(s). -- Andreas Joseph Krogh CTO / Partner - Visena AS 
Mobile: +47 909 56 963 andr...@visena.com  
www.visena.com   

Re: PostgreSQL License

[Rob Sargent]

> On Sep 17, 2019, at 4:18 PM, Andreas Joseph Krogh  wrote:
> 
> På tirsdag 17. september 2019 kl. 22:55:02, skrev Peter J. Holzer 
> mailto:hjp-pg...@hjp.at>>:
> On 2019-09-17 14:56:30 +0300, Ashkar Dev wrote:
> > but it is saying (without fee)
> > if I create a database with it to work with Web Application if want to sell 
> > it
> > so the buyer must have the PostgreSQL installed in his device to work 
> > offline
> > right?
> > "Permission to use, copy, modify, and distribute this software and its
> > documentation for any purpose, without fee, and without a written agreement 
> > is
> > hereby granted, provided that the above copyright notice and this paragraph 
> > and
> > the following two paragraphs appear in all copies."
> 
> This means that you don't have to pay a fee or sign a written agreement
> to use, copy, modify, and distribute this software and its documentation
> for any purpose. It doesn't say that you can't charge a fee for
> distributing (although why anybody would pay you for something they can
> download themselves for free I don't know).
> 
> hp
>  
> A rule of thumb is - you can do anything you want with it (the PG software 
> inc. its source), except claim you wrote it, as long as you preserve the 
> original license-file(s).
>  
I take it that the OP has an app/dataset on top of PG he/she wishes to market 
(and protect).  Perfectly legit, no? Not clear if there is a desire to disable 
direct db access.

Re: PostgreSQL License

[Andreas Joseph Krogh]

På onsdag 18. september 2019 kl. 01:07:41, skrev Rob Sargent <
robjsarg...@gmail.com >: On Sep 17, 2019, at 4:18 
PM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote:
På tirsdag 17. september 2019 kl. 22:55:02, skrev Peter J. Holzer <
hjp-pg...@hjp.at >: On 2019-09-17 14:56:30 +0300, 
Ashkar Dev wrote:
 > but it is saying (without fee)
 > if I create a database with it to work with Web Application if want to sell 
it
 > so the buyer must have the PostgreSQL installed in his device to work 
offline
 > right?
 > "Permission to use, copy, modify, and distribute this software and its
 > documentation for any purpose, without fee, and without a written agreement 
is
 > hereby granted, provided that the above copyright notice and this paragraph 
and
 > the following two paragraphs appear in all copies."

 This means that you don't have to pay a fee or sign a written agreement
 to use, copy, modify, and distribute this software and its documentation
 for any purpose. It doesn't say that you can't charge a fee for
 distributing (although why anybody would pay you for something they can
 download themselves for free I don't know).

 hp A rule of thumb is - you can do anything you want with it (the PG software 
inc. its source), except claim you wrote it, as long as you preserve the 
original license-file(s).  I take it that the OP has an app/dataset on top of 
PG he/she wishes to market (and protect). Perfectly legit, no? Not clear if 
there is a desire to disable direct db access. That seems perfectly legit. I'm 
not sure what "to work offline" means, but using PG for whatever commercial 
purposes is totally fine, given the license-requirement above. -- Andreas 
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com
  www.visena.com   

Re: pgbackrest restore to new location?

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 9/17/19 6:48 AM, David Steele wrote:
> >On 9/17/19 7:23 AM, Luca Ferrari wrote:
> >>On Tue, Sep 17, 2019 at 12:00 PM Ron  wrote:
> >>>The real problem is that after doing that, "pg_ctl start -D
> >>>/path/to/new/data" fails with "PANIC: could not locate a valid checkpoint
> >>>record".
> >Sounds like backup_label is missing or has been deleted.
> >
> >The easiest way to restore to a new location is just to copy
> >pgbackrest.conf from the primary (or create a new one) with the same
> >stanza and then alter pg1-path in pgbackrest.conf or at the command line.
> 
> That's what I did.  (Also, I opened Issue #839 in GitHub  All of the log
> files are attached there.)

Per the discussion in that issue, it looks like there's some issue with
the restore command failing to be able to pull the needed WAL from the
repo.

That said- it brings up a pretty serious issue that should be discussed,
and that's nuking this:

HINT:  If you are not restoring from a backup, try removing the file 
".../backup_label".

That hint is absolutely wrong these days when many tools have been
updated to use the non-exclusive backup method and it just ends up
getting people into trouble and, worse, can result in them having
corrupted clusters.

I'll get a patch into the next commitfest to remove it.  The exclusive
method has been deprecated for quite a few releases and we should stop
giving bad advice on the assumption that people are using it.

Thanks,

Stephen


signature.asc
Description: PGP signature

When does Postgres use binary I/O?

[Paul A Jungwirth]

Hello,

I've read the docs at [1] and also this interesting recent post about
adding binary I/O to the hashtypes extension. I wrote send & recv
functions for my new multirange types, but I'm not sure how to test
them. After running installcheck or installcheck-world, the code
coverage report says they aren't tested, nor are the send/recv
functions for ranges or arrays. When does Postgres actually use these
functions? Is there a good way to test them?

Thanks,
Paul

[1] https://www.postgresql.org/docs/11/xtypes.html
[2] 
http://www.myrkraverk.com/blog/2019/08/postgresql-retroactively-adding-binary-i-o-to-a-type/