Re: Are all unlogged tables in any case truncated after a server-crash?

[sch8el]

Hi David,

thx for your comments and your advice on reading docs on "checkpoint".

Of course consistency is most important to any DBMS, and if in doubt 
about that, truncate data rows and restore from WAL.
But in this case, where data is never modified after bulk load, I 
thought there might be an undocumented feature or workaround, like ...
  - option to set the datafiles of those tables in read-only mode and 
record this in the metadata
  - on server-recovery spare these unlogged tables and indexes from 
truncating all data rows


Its truly a "nice to have"-thing, but I have learned now, that there is 
not feature like that.


Mart


Am 11.11.2021 um 22:10 schrieb David G. Johnston:

On Thu, Nov 11, 2021 at 11:39 AM  wrote:

After
that all unlogged tables remain completely unchanged (no
DML-/DDL-Statements). Hence all of my huge unlogged, "static"
tables get
never "unclean" and should not be truncated after a server crash.


The server cannot make this assumption so it truncates unlogged 
relations upon an unclean shutdown/crash because it has no WAL with 
which to ensure a proper restoration.


BTW, if I set all unlogged tables to logged after bulk load, it takes
additional 1.5 hours, mainly because of re-indexing, I suppose.


More likely it is writing the entire table, and all of its indexes, to 
WAL.


I assume
that a restart of the database after a server crash takes another 1.5
hours (reading from WAL) until the database is up and running.


That would be incorrect.  See "CHECKPOINT".


Therefore I am seeking a strategy, to not tagging those tables as
"unclean" and not truncating all unlogged tables on server restart.


There is no middle ground that I am aware of.  Either the contents of 
the table are in WAL ,or they are not.  If not, they can be lost upon 
an unclean shutdown.  For manually initiated shutdowns you do have the 
option to do so cleanly.


This topic (unlogged optimizations) does draw quite a bit of attention 
every year but so far the problem of proving to the system that the 
physical file on disk is a truly accurate representation of the 
post-crash relation is yet unsolved.


David J.

Re: Pg_hba.conf problem after unexpected IP change

[Yessica Brinkmann]

 Hello.
Thank you very much for your answer.
Yes, I restart the server after making the changes.
Regards,
Yessica Brinkmann


Libre
de virus. www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

El jue, 11 nov 2021 a las 22:41, Adrian Klaver ()
escribió:

> On 11/11/21 17:14, Yessica Brinkmann wrote:
> > Hello. I write for the following reason: I had configured a pg_hba.conf
> > file, which I am using with some debian virtual machines, to be able to
> > communicate between two different virtual machines.
>
> > Well, then, I went to modify my pg_hba.conf file so that it reflected
> > the new IP that I did not know why it changed, and to continue
> > communicating between the two virtual machines, but it turns out that
> > when I change my pg_hba.conf file, the connection still does not work
> > for me .
>
> Did you reload or restart the server after making the change?
>
> > I clarify that right now when doing ip addr in the virtual machine whose
> > IP changed unexpectedly, the following IP address appears:
> > 192.168.52.153/24 .
> > I will greatly appreciate a help please.
> > Regards,
> > Yessica Brinkmann
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Are all unlogged tables in any case truncated after a server-crash?

[sch8el]

Am 12.11.2021 um 08:41 schrieb Laurenz Albe:

On Thu, 2021-11-11 at 18:39 +, sch...@posteo.de wrote:

every few weeks I use Postgres ability, to import huge data sets very
fast by means of "unlogged tables". The bulk load (consisting of plenty
"copy"- & DML-Stmts) and the spatial index creation afterwards, takes
about 5 hours on a proper server  (pg12.7 & PostGIS-Extension). After
that all unlogged tables remain completely unchanged (no
DML-/DDL-Statements). Hence all of my huge unlogged, "static" tables get
never "unclean" and should not be truncated after a server crash.

There is no way to achieve that.

But you could keep the "huge data sets" around and load them again if
your server happens to crash (which doesn't happen often, I hope).
Thx Laurenz for yr reply! Yes, that's what we did after server crashes 
(~ 2/yr on different locations).
But the system is at least 5 hours offline plus the time until the admin 
manually re-starts the bulk loads. On my system, I have 6 databases 
configured like this. For all I have to redo the bulk loads.
I hoped there was a 'switch' on crash-recovery, to avoid truncating the 
datafiles of these unlogged tables, which are definitely in a perfect 
condition.


Mart


Yours,
Laurenz Albe

Re: Pg_hba.conf problem after unexpected IP change

[Dave Cramer]

On Fri, 12 Nov 2021 at 06:12, Yessica Brinkmann 
wrote:

> Hello.
> Thank you very much for your answer.
> Yes, I restart the server after making the changes.
> Regards,
> Yessica Brinkmann
>

Check the logs for postgres to make sure there were no errors in your new
configuration.

Dave Cramer
www.postgres.rocks

>
>>

Re: Are all unlogged tables in any case truncated after a server-using

[Michael Lewis]

Why keep them as unlogged tables? If data is static, can you spare the disk
space to gradually copy data from existing unlogged table to new copy that
is logged, and then have brief exclusive lock to drop unlogged and rename
new one?

Replace anonymized data in string

[Patrick FICHE]

Hi Team,

I have some data that has been anonymized and I would like to generate some 
test data from this. In some way, I would like to deanonymize this data with 
random data.

For example, phone numbers have been anonymized with changing the 5 right 
digits with the 8 digit (preserving length).
Applying this, the number 390694802756 was changed to 390694808.

I would like to get random digits at the end of the phone number knowing that 
anonymized data can be a variable length.
So, I would like to change every sequence (at least 2) of 8 by random value of 
same length (I don't worry if phone number contains 88 in the middle and this 
sequence is changed to random data)...

I tried to do this with replace / regexp_replace functions but could not 
achieve what I wanted to do.
I don't want these digits to be changed with a single one (8 by 11 or 
9 but something like 42384)...
Ideally, the new string would be different if multiple sequences of 8 appear in 
a string and would be different from one record to the other when applied to a 
full table...

Is there any way to do this ?

Thanks,
Patrick

Re: Replace anonymized data in string

[Daniel Gustafsson]

> On 12 Nov 2021, at 15:12, Patrick FICHE  wrote:

> Is there any way to do this ?

There was a presentation on the subject of anonymization and data masking at
Fosdem PGDay 2019, maybe the slides from there can give any insights?

https://www.postgresql.eu/events/fosdem2019/schedule/session/2287-anonymization-and-data-masking-with-postgresql/

--
Daniel Gustafsson   https://vmware.com/

Re: Replace anonymized data in string

[Rob Sargent]

On 11/12/21 7:12 AM, Patrick FICHE wrote:


Hi Team,

I have some data that has been anonymized and I would like to generate 
some test data from this. In some way, I would like to deanonymize 
this data with random data.


For example, phone numbers have been anonymized with changing the 5 
right digits with the 8 digit (preserving length).


Applying this, the number 390694802756 was changed to 390694808.

I would like to get random digits at the end of the phone number 
knowing that anonymized data can be a variable length.


So, I would like to change every sequence (at least 2) of 8 by random 
value of same length (I don’t worry if phone number contains 88 in the 
middle and this sequence is changed to random data)…


I tried to do this with replace / regexp_replace functions but could 
not achieve what I wanted to do.


I don’t want these digits to be changed with a single one (8 by 
11 or 9 but something like 42384)…


Ideally, the new string would be different if multiple sequences of 8 
appear in a string and would be different from one record to the other 
when applied to a full table…


Is there any way to do this ?

Thanks,

Patrick


Usual trick it to select floor(random()*10);

RE: Replace anonymized data in string

[Patrick FICHE]

> On 12 Nov 2021, at 15:12, Patrick FICHE  wrote:

> Is there any way to do this ?

There was a presentation on the subject of anonymization and data masking at 
Fosdem PGDay 2019, maybe the slides from there can give any insights?

https://www.postgresql.eu/events/fosdem2019/schedule/session/2287-anonymization-and-data-masking-with-postgresql/

--

Thanks a lot for your answer.
This is a very good presentation for anonymization technics.
Unfortunately, my data has been already anonymized and I'm trying to random the 
anonymized part... which is a bit different from what I could find here 😊

Re: Are all unlogged tables in any case truncated after a server-crash?

[Michael Lewis]

Curious... why keep the table as unlogged if it is static? If you can spare
the disk space, perhaps just create a regular table with same definition,
gradually copy the data to spread the impact on WAL, and when complete,
just drop the old table and rename the new one.

Re: Pg_hba.conf problem after unexpected IP change

[Yessica Brinkmann]

 Thank you very much for your answer.
Regards,
Yessica Brinkmann


El vie, 12 nov 2021 a las 9:22, Dave Cramer ()
escribió:

>
>
>
> On Fri, 12 Nov 2021 at 06:12, Yessica Brinkmann <
> brinkmann.yess...@gmail.com> wrote:
>
>> Hello.
>> Thank you very much for your answer.
>> Yes, I restart the server after making the changes.
>> Regards,
>> Yessica Brinkmann
>>
>
> Check the logs for postgres to make sure there were no errors in your new
> configuration.
>
> Dave Cramer
> www.postgres.rocks
>
>>
>>>

Re: Pg_hba.conf problem after unexpected IP change

[Adrian Klaver]

On 11/12/21 10:34 AM, Yessica Brinkmann wrote:

Thank you very much for your answer.


Did you find the problem?

If not have you investigated whether whatever changed the IP also 
enabled a firewall rule that blocks port(5432 I'm assuming)?



Regards,
Yessica Brinkmann


El vie, 12 nov 2021 a las 9:22, Dave Cramer 
() escribió:





On Fri, 12 Nov 2021 at 06:12, Yessica Brinkmann
mailto:brinkmann.yess...@gmail.com>>
wrote:

Hello.
Thank you very much for your answer.
Yes, I restart the server after making the changes.
Regards,
Yessica Brinkmann


Check the logs for postgres to make sure there were no errors in
your new configuration.

Dave Cramer
www.postgres.rocks





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

How to Implement DR for a Production PostgreSQL v12.8 database cluster with repmgr & PgBouncer?

[Hilbert, Karin]

I manage PostgreSQL v12.8 database clusters.
Our database clusters are on Linux VMs, with OS:
  Flavor: redhat_7
  Release: 3.10.0-1160.45.1.el7.x86_64

We have repmgr clusters of 1 Primary & 2 Standby servers & use another server 
with PgBouncer to direct the connections to the current Primary.

The PostgreSQL servers have asynchronous replication & use repmgr to handle 
automatic failovers.
Any failovers have always promoted the 1st Standby server in the cluster.
We did have one time where the newly promoted server almost immediately went 
down & the 2nd Standby was promoted.
All three servers in the cluster currently have a priority of 100.

I've been requested to set up DR for the Production cluster.  My Google 
searches haven't found anything describing how DR should be implemented.  I 
have seen that there should always be an odd number of servers in the cluster.

My thoughts are to have a new VM created in our DR datacenter that will replace 
the 2nd Standby server in the cluster.  I'm thinking that the DR server would 
have a lower priority in the repmgr.conf file (50 instead of 100), since we 
would want the local Standby to be promoted first.  We have failover configured 
to be automatic, but it waits 80 seconds before promoting a Standby (to avoid a 
premature failover due to network flapping).

Is there any reason to change the failover configuration to manual for DR?  I 
would think in a DR situation we would want it to be automatic, but my 
colleague disagrees.  I'm thinking that if the Primary & the 1st Standby both 
go down, even if it isn't a real "DR" situation, we would still want it to 
promote to the 3rd (DR) server to prevent an outage.  Our failover script 
performs post-promote tasks, including redirecting the PgBouncer server to 
point to the new Primary, so it shouldn't matter if it was pointing to the DR 
server or a local server.

If we do decide to make failover manual, can the configuration specify 
automatic for the local servers & manual for the DR server, or is it an all or 
nothing type of configuration?

I guess another strategy could be to configure it as a stand-alone server & 
leave our current cluster intact.  Then copy all the backup & WAL files to the 
DR server, but then in a DR situation we would have to perform the restore 
before the databases could be available.

Any advice on a good DR strategy would be appreciated.
Thanks,

Karin Hilbert