Re: Backup & Restore

[sivapostg...@yahoo.com]

 HiCan u suggest a good backup solution for a windows installation ?  Looks 
like the suggested two [ pgbarman, pgbackrest ] works only in Linux.
On Tuesday, 25 February, 2020, 01:46:33 am IST, Stephen Frost 
 wrote:  
 
 Greetings,

* Dor Ben Dov (dor.ben-...@amdocs.com) wrote:
> What is your backup and restore solution in production when working with 
> Postgres ?
> (+ if you can say few words why you picked this X solution instead of others)

I'd recommend pgbackrest- https://www.pgbackrest.org, it's got lots of
great features including parallel backup, incremental and differential
backups, compression, encryption, and all of those can be used together.
pgbackrest also can parallelize WAL shipping if you're writing lots of
data.

There's other options out there, of course.  In any case, I strongly
recommend that you use one of the existing solutions and don't try to
roll your own.

Just to be clear- I'm also involved in the project (though not the
primary developer, that's David, who you'll also see on this list and on
the -hackers list contributing things to PostgreSQL).

Thanks!

Stephen
  

Re: LDAP with TLS is taking more time in Postgresql 11.5

[Adrian Klaver]

On 2/24/20 11:50 AM, Mani Sankar wrote:

Hi All,

We have recently upgraded our postgres servers from 9.4 version to 11.5 
version. Post upgrade we are see delay in authentication.


Issue is when we are using ldaptls=1 the authentication takes 1 second 
or greater than that. But if I disable ldaptls it's getting 
authenticated within milliseconds.


But in 9.4 even if I enable ldaptls it's getting authenticated within 
milliseconds any idea why we are facing the issue?


This is going to need a good deal more information:

1) OS the server is running on and did the OS or OS version change with 
the upgrade?


2) How was the server installed from packages(if so from where?) or from 
source?


3) The configuration for LDAP in pg_hba.conf.

4) Pertinent information from the Postgres log.

5) Pertinent information from the system log.



Regards,
Mani.




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

Re: Backup & Restore

[Stephen Frost]

Greetings,

* Dor Ben Dov (dor.ben-...@amdocs.com) wrote:
> What is your backup and restore solution in production when working with 
> Postgres ?
> (+ if you can say few words why you picked this X solution instead of others)

I'd recommend pgbackrest- https://www.pgbackrest.org, it's got lots of
great features including parallel backup, incremental and differential
backups, compression, encryption, and all of those can be used together.
pgbackrest also can parallelize WAL shipping if you're writing lots of
data.

There's other options out there, of course.  In any case, I strongly
recommend that you use one of the existing solutions and don't try to
roll your own.

Just to be clear- I'm also involved in the project (though not the
primary developer, that's David, who you'll also see on this list and on
the -hackers list contributing things to PostgreSQL).

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Backup & Restore

[Andreas Kretschmer]

Am 24.02.20 um 09:18 schrieb Dor Ben Dov:


Hi All,

What is your backup and restore solution in production when working 
with Postgres ?




most of our customers using Barman: https://www.pgbarman.org/


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

LDAP with TLS is taking more time in Postgresql 11.5

[Mani Sankar]

Hi All,

We have recently upgraded our postgres servers from 9.4 version to 11.5
version. Post upgrade we are see delay in authentication.

Issue is when we are using ldaptls=1 the authentication takes 1 second or
greater than that. But if I disable ldaptls it's getting authenticated
within milliseconds.

But in 9.4 even if I enable ldaptls it's getting authenticated within
milliseconds any idea why we are facing the issue?

Regards,
Mani.

Re: Reset DB stats suggestion pg_stat_reset()

[Michael Lewis]

>
> We have seen some deadlocks and tempfile count in pg_stat_database view.
> We are trying to reset the stats.
> Can we use pg_stat_reset() function to reset these stats without any
> impact stats of databases.
> Please advise the process to reset the stats.
>

Yes, you could reset stats every month or week or whatever you decide makes
sense so you have an understanding of the timeline. Else, knowing that an
index has been used X times since who-knows-when is not a very useful piece
of information. I have occasionally reset the stats on all indexes on a
table when adding or removing an index from that table so that I have an
accurate idea of whether index_xyz or index_abc gets used. You can take a
snapshot of the stats on the table occasionally and compare prior count to
new count if you prefer. I find reset to be cleaner.

Re: backup broken

[bvo]

I correct the pg_hba.conf 
host all all 0.0.0.0/0 md5 to trust and the backup script work as root and
postgres but pgAdmin4 stop launched.
I needed to change all from ident to trust and add 
host all all 0.0.0.0/0  trust

all working fine now.

thank you.
Bach-Nga



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

backup broken

[bvo]

Hello,

my pg_backup_rotated.sh script ran fine since Dec 20 and after rebuild the
slave now it's broken and I do not know why.  I can run pg_dumpall >
/tmp_all_db_backup.sql but not from the script. From the log its pg_dumall:
could not connect to database "template1": FATAL: Ident authentication
failed for user "postgres".  I tried to reset postgres in psql as "alter
user postgres with PASSWORD 'x'; (as same as the old one and the
postgres os).  
I run pg_backup_rotated.sh as postgres and it promp me for the password and
then password for user postges and repeat it again and again until all the
databases backup.  

>From the script it gives me pg_dumpall: could not connect to database
"template1": FATAL: password authentication failed for user "postgres" and
even I run the script as root, it prompted me for password,
it gives me pg_dumpall: could not connect to database "template1": FATAL:
password authentication failed for user "postgres"

Typically, I make a backup based on the current day, and rotate the seven
days in the backup file name, all successful until Feb 19.

How can I resolve this issue? Any good backup tips I should be aware of. 

Thanks for all the tips

Bach-Nga



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

Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

[Tom Lane]

"Nick Renders"  writes:
> We have set up a new test environment running PostgreSQL v12.2 on macOS 
> 10.14 and the issue is still there.

Some nearby threads prompt these two questions:

1. Are you using your own build, or is this from EDB's installer?

2. If the latter, does turning JIT off ("set jit = off") make the
problem go away?

There is as yet no "native" support for --with-llvm on macOS,
ie Apple themselves don't provide sufficient support for that.
EDB seem to have hacked up something that sort of works, but
only sort of.

regards, tom lane

Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

[Nick Renders]

We have set up a new test environment running PostgreSQL v12.2 on macOS 
10.14 and the issue is still there.
One thing I noticed, is that the returning columns do not affect the 
behaviour:


SELECT * FROM f_gsxws_schedule WHERE UPPER(gwsc_dossier) = 'TEST'

and
	SELECT gwsc_sequence FROM f_gsxws_schedule WHERE UPPER(gwsc_dossier) = 
'TEST'


both kill the postgres service.


I will try to free some time next week to install the Apple developer 
tools and further analyse the problem.


Best regards,

Nick


On 11 Feb 2020, at 12:32, Nick Renders wrote:


Hi Thomas,

We are setting up a new test environment with 12.1.
Once it is running, I'll try out those commands and get back with the 
results.


Thanks,

Nick Renders


On 11 Feb 2020, at 2:51, Thomas Munro wrote:


On Mon, Feb 10, 2020 at 4:35 AM Marc  wrote:
We will keep the 12.1 in place so that we can run additional tests 
to assist to pin-point the issue.


Feel free to ask but allow us to recover from these hectic days ;-)


Here's how to get a stack so we can see what it was doing, assuming
you have the Apple developer tools installed:

1.  Find the PID of the backend you're connected to with SELECT
pg_backend_pid().
2.  "lldb -p PID" from a shell to attach to the process, then "cont"
to let it continue running.
3.  Run the query in that backend and wait for the SIGKILL.
4.  In the lldb session, type "bt".

It'll only make sense if your PostgreSQL build has debug symbols, but 
let's see.

Re: Can I trigger an action from a coalesce ?

[Peter J. Holzer]

On 2020-02-24 05:20:49 +, sivapostg...@yahoo.com wrote:
> It could also be done by putting those values in square bracket, if 
> substituted
> with default values.
> eg. [0]
>4
>45
> [100]
> Values within square brackets are default values.

This would also work in many cases (especially if the values only have
to be displayed and not processed further).

In this case the OP wrote that "the default value is reasonable and
could actually come from the source table". I assumed that he had a
reason for this choice and wanted to preserve it.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Backup & Restore

[Dor Ben Dov]

Hi All,

What is your backup and restore solution in production when working with 
Postgres ?
(+ if you can say few words why you picked this X solution instead of others)

Regards,
Dor
This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service