Re: decompose big queries

[Edson Carlos Ericksson Richter]

Em 05/04/2018 19:39, hmidi slim escreveu:

Hi,
I want to know what are the best practice to use in order to decompose 
a big query which contains so many joins.Is it recommended to use 
stored procedures ? or is there any other solution?




I don't know if there are best practices (each scenario requires its own 
solution), but for plain complex SELECT queries, I do use "WITH" 
queries... They work really well.


https://www.postgresql.org/docs/9.6/static/queries-with.html


Regards,

Edson

Re: Postgresql Split Brain: Which one is latest

[Edson Carlos Ericksson Richter]

Em 10/04/2018 12:28, Melvin Davidson escreveu:



On Tue, Apr 10, 2018 at 11:04 AM, Vikas Sharma > wrote:


Hi Adrian,

This can be a good example: Application server e.g. tomcat having
two entries to connect to databases, one for master and 2nd for
Slave (ideally used when slave becomes master). If application is
not able to connect to first, it will try to connect to 2nd.

Regards
Vikas

On 10 April 2018 at 15:26, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 04/10/2018 06:50 AM, Vikas Sharma wrote:

Hi,

We have postgresql 9.5 with streaming
replication(Master-slave) and automatic failover. Due to
network glitch we are in master-master situation for quite
some time. Please, could you advise best way to confirm
which node is latest in terms of updates to the postgres
databases.


It might help to know how the two masters received data when
they where operating independently.


Regards
Vikas Sharma



-- 
Adrian Klaver

adrian.kla...@aklaver.com 



*Vikas,

*
*Presuming the the real "master" will have additional records/rows 
inserted in the tables,

*
*if you run ANALYZE on the database(s) in both "masters", then execute 
the following query

*
*on both, whichever returns the highest count would be the real "master".

 SELECT sum(c.reltuples::bigint)
    FROM pg_stat_all_tables s
      JOIN pg_class c ON c.oid = s.relid
 WHERE s.relname NOT LIKE 'pg_%'
   AND s.relname NOT LIKE 'sql_%';*


--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC*
Employment by invitation only!


I'm just trying to understand the scenario...

Correct me if I'm wrong, if you had two servers acting as master for a 
while, then you have inserted/updated records on both servers, and you 
will need some kind of "merge" of records into one of the databases, 
that will become the new updated master...


If you have "sequences" (or "serial" fields), then you will get a bit 
trouble in your hands.


Regards,

Edson

Re: We find few queries running three times simultaneously with same parameters on postgres db

[Edson Carlos Ericksson Richter]

Em 26/06/2018 08:49, Saurabh Agrawal escreveu:



Am 26.06.2018 um 12:19 schrieb amandeep singh:

We have been observing our postgres database from past few
days,We found few queries running three times simultaneously
with same parameters.I would like to back track how a query is
running multiple times.


Can you check the requests made by your application/ ORM? This looks 
like application is making multiple requests, rather than something 
happening on the database?




I agree with Saurabh Agrawal.
Sometimes, users just double (or triple) click a form button, and then 
resulting activity is executed more than once...
What I used is to deactivate buttons after first click. This is even 
more noticeable when working on intranet apps ou high speed internet 
connections.


Users, sometimes, need to be educated that one click is enough :-)

Regards,

Edson.

Re: We find few queries running three times simultaneously with same parameters on postgres db

[Edson Carlos Ericksson Richter]

Em 26/06/2018 14:26, amandeep singh escreveu:

Hi Andreas

The value for $1 is same in all queries.

@Edson: number of such running queries are always  3.


I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with 
EclipseLink, didn't test with Hibernate):


1) In persistence.xml, disable all caches (this is very important for 
make reproducible)
2) Load three Person records that live on same city (objects Person and 
City mapped to tables Person and City, being city an attribute of Person 
object):


TypedQuery qry = em.createQuery("select P from Person P where 
P.city.name = :cityname");

qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List rs = qry.getResultList();

3) This will cause one query against Person table, and exactly 3 queries 
issued with same parameters to City table.


I really don't know how your code works, and is quite hard to guess, but 
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each 
City key - so, I'll have one query for Person and one query for  only.


Hope this helps to enlighten your problem.

:-)

Regards,

Edson

Re: unorthodox use of PG for a customer

[Edson Carlos Ericksson Richter]

Em 24/08/2018 15:18, David Gauthier escreveu:

Hi Everyone:

I'm going to throw this internal customer request out for ideas, even 
though I think it's a bit crazy.  I'm on the brink of telling him it's 
impractical and/or inadvisable.  But maybe someone has a solution.


He's writing a script/program that runs on a workstation and needs to 
write data to a DB.  This process also sends work to a batch system on 
a server farm external to the workstation that will create multiple, 
parallel jobs/processes that also have to write to the DB as well. The 
workstation may have many of these jobs running at the same time.  And 
there are 58 workstation which all have/use locally mounted disks for 
this work.


At first blush, this is easy.  Just create a DB on a server and have 
all those clients work with it.  But he's also adamant about having 
the DB on the same server(s) that ran the script AND on the locally 
mounted disk.  He said he doesn't want the overhead, dependencies and 
worries of anything like an external DB with a DBA, etc... . He also 
wants this to be fast.
My first thought was SQLite.  Apparently, they now have some sort of 
multiple, concurrent write ability.  But there's no way those batch 
jobs on remote machines are going to be able to get at the locally 
mounted disk on the workstation. So I dismissed that idea. Then I 
thought about having 58 PG installs, one per workstation, each serving 
all the jobs pertaining to that workstation.  That could work.  But 58 
DB instances ?  If he didn't like the ideal of one DBA, 58 can't be 
good.  Still, the DB would be on the workstation which seems to be 
what he wants.

I can't think of anything better.  Does anyone have any ideas?

Thanks in Advance !



I'm no expert, but I've dozens of PostgreSQL databases running mostly 
without manual maintenance for years, just do the backups, and you are fine.
In any way, if you need any kind of maintenance, you can program it in 
your app (even backup, restore and vacuum) - it is easy to throw 
administrative commands thru the available interfaces.
And if the database get out of access, no matter if it is centralized or 
remote: you will need someone phisically there to fix it.
AFAIK, you don't even PostgreSQL installer - you can run it embed if you 
wish.


Just my2c,

Edson

Re: unorthodox use of PG for a customer

[Edson Carlos Ericksson Richter]

Em 24/08/2018 16:07, David Gauthier escreveu:

I tried to convince him of the wisdom of one central DB.  I'll try again.

>>So are the 58 database(stores) on the workstation going to be working
with data independent to each or is the data shared/synced between
instances?

No, 58 workstations, each with its own DB.  There's a concept of a 
"workarea" (really a dir with a lot of stuff in it) where the script 
runs.  He wants to tie all the runs for any one workarea together and 
is stuck on the idea that there should be a separate DB per workarea.  
I told him you could just stick all the data in the same table just 
with a "workarea" column to distinguish between the workareas.  He 
likes the idea of a separate DB per workarea.  He just doesn't gt it.


>>I'm no expert, but I've dozens of PostgreSQL databases running mostly
without manual maintenance for years.

Ya, I've sort of had the same experience with PG DBs.  Like the 
everready bunny, they just keep on running.  But these workstations 
are pretty volatile as they keep overloading them and crash them.  Of 
course any DB running would die too and have to be 
restarted/recovered.  So the place for  the DB is really elsewhere, on 
an external server that wouldn't be subject to this volatility and 
crashing.  I told him about transactions and how you could prevent 
partial writing of data sets.


So far, I'm not hearing of anything that looks like a solution given 
the constraints he's put on this.  Don't get me wrong, he's a very 
smart and sharp software engineer.  Very smart.  But for some reason, 
he doesn't like the client/server DB model which would work so nicely 
here.  I'm just trying to make sure I didn't miss some sort of 
solution, PG or not, that would work here.


Thanks for your interest and input everyone !




On Fri, Aug 24, 2018 at 2:39 PM Edson Carlos Ericksson Richter 
mailto:rich...@simkorp.com.br>> wrote:


Em 24/08/2018 15:18, David Gauthier escreveu:
> Hi Everyone:
>
> I'm going to throw this internal customer request out for ideas,
even
> though I think it's a bit crazy.  I'm on the brink of telling
him it's
> impractical and/or inadvisable.  But maybe someone has a solution.
>
> He's writing a script/program that runs on a workstation and
needs to
> write data to a DB.  This process also sends work to a batch
system on
> a server farm external to the workstation that will create
multiple,
> parallel jobs/processes that also have to write to the DB as
well. The
> workstation may have many of these jobs running at the same
time.  And
> there are 58 workstation which all have/use locally mounted
disks for
> this work.
>
> At first blush, this is easy.  Just create a DB on a server and
have
> all those clients work with it.  But he's also adamant about having
> the DB on the same server(s) that ran the script AND on the locally
> mounted disk.  He said he doesn't want the overhead,
dependencies and
> worries of anything like an external DB with a DBA, etc... . He
also
> wants this to be fast.
> My first thought was SQLite.  Apparently, they now have some
sort of
> multiple, concurrent write ability.  But there's no way those batch
> jobs on remote machines are going to be able to get at the locally
> mounted disk on the workstation. So I dismissed that idea. Then I
> thought about having 58 PG installs, one per workstation, each
serving
> all the jobs pertaining to that workstation.  That could work. 
But 58
> DB instances ?  If he didn't like the ideal of one DBA, 58 can't be
> good.  Still, the DB would be on the workstation which seems to be
> what he wants.
> I can't think of anything better.  Does anyone have any ideas?
>
> Thanks in Advance !
>

I'm no expert, but I've dozens of PostgreSQL databases running mostly
without manual maintenance for years, just do the backups, and you
are fine.
In any way, if you need any kind of maintenance, you can program
it in
your app (even backup, restore and vacuum) - it is easy to throw
administrative commands thru the available interfaces.
And if the database get out of access, no matter if it is
centralized or
remote: you will need someone phisically there to fix it.
AFAIK, you don't even PostgreSQL installer - you can run it embed
if you
wish.

Just my2c,

Edson


I think its worth to add, PG or not PG, if the workstation crash, you 
will be in trouble with ANY database or file solution you choose - but 
with PG you can minimize the risk by fine tunning the flush to disk 
(either in PG and in OS). When correctly tuned, it works like a tank, 
and is hard to defeat.


Regards,

Edson.

Re: ORM

[Edson Carlos Ericksson Richter]

Em 28/09/2018 19:49, marcelo escreveu:
For a new big and convoluted project I (am/was) using Devart´s 
LinqConnect as ORM.
But today I experienced some inexplicable "object reference not set to 
an instance of an object" exceptions or other more specific to this 
libraries.

I would wish to change the ORM.
Some experiences would be appreciated. Of course, I prefer open source 
software, at least to chase errors thru debugging.

TIA

---
El software de antivirus Avast ha analizado este correo electrónico en 
busca de virus.

https://www.avast.com/antivirus





For .Net I know nothing.
For Java, at other side, EclipseLink is fantastic.

Regards,

Edson.

Re: Barman disaster recovery solution

[Edson Carlos Ericksson Richter]

Em 21/02/2019 04:17, Julie Nishimura escreveu:

Does anyone use this solution? any recommenations?

Thanks!



We do use it.

IMHO, those are minimum recommendations:


1) start using it! It's easy and robust.

2) for minimal impact over production servers, setup replicated servers 
and create your backup from slave servers.


3) *_test your backups_*. This is a MUST HAVE - no option here.

4) have your backup server in different cities, or states, or even 
countries. Never, ever create a backup on the server at the side of your 
production server.


5) only communicate with your servers using SSH and private key 
certificates. Establish a PKI infrastructure in a way that production 
and backup servers only communicate using SSH and certificates.


6) your backup servers shall never ever be connected directly to the 
internet. Hackers love low attention backup servers running with minimal 
security.



No backup solution (no matter which one you choose) is 100% guaranteed: 
your disks may fail, your network mail fail, your memory may fail, files 
get corrupted - so, setup a regular "restore" to separate "test backup 
server" on daily basis. Having a virtual server for this purpose has 
minimal budget impact if any at all, and you save your sanity in case of 
a disaster.




Regards,



Edson

Re: Barman disaster recovery solution

[Edson Carlos Ericksson Richter]

Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:
Just to notice, I d o use backup from standby and WAL archive from 
standby. It is possible. But you have to configure standby with 
option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman 
and pgbackrest would support it out of the box.




Well,


This setup it is working really well for past two years; prior to that, 
we used backup from primary server.


We have about 50 databases, half terabyte in total, primary and standby 
separated geographically.


We had to write special app to monitor if standby is behind primary (it 
compares the transaction id between primary and standby).


For eight years, we had no single failure from PgSQL databases (using 
since 9.0 and today on 9.6), replication is for "just in case" data 
center unavailability, and backup is for disaster recovery (in case two 
data centers in different states from different vendors get out of work 
at same time).



But we give no chance to bad luck: we monitor replication status every 2 
minutes, we make full backup every 2 days with incremental backup in 
between, and test all backups on a recover server every day. As pointed, 
we have no single database failure that required to use the replication 
server or the backup server, but we will not lower the attention.



Regards,


Edson






Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

-- Mensagem original--
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org 
;

*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL 
traffic goes on 10 files/day or more. I have written an article, 
not yet publised, on a comparison on the 3 most known solutions. 
Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql



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



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



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

Re: Barman disaster recovery solution

[Edson Carlos Ericksson Richter]

Em 27/02/2019 12:12, Achilleas Mantzios escreveu:

On 27/2/19 5:04 μ.μ., Edson Carlos Ericksson Richter wrote:


Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:
Just to notice, I d o use backup from standby and WAL archive from 
standby. It is possible. But you have to configure standby with 
option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman 
and pgbackrest would support it out of the box.




Well,


This setup it is working really well for past two years; prior to 
that, we used backup from primary server.

Using which tool?



Barman.




We have about 50 databases, half terabyte in total, primary and 
standby separated geographically.


We had to write special app to monitor if standby is behind primary 
(it compares the transaction id between primary and standby).


For eight years, we had no single failure from PgSQL databases (using 
since 9.0 and today on 9.6), replication is for "just in case" data 
center unavailability, and backup is for disaster recovery (in case 
two data centers in different states from different vendors get out 
of work at same time).



But we give no chance to bad luck: we monitor replication status 
every 2 minutes, we make full backup every 2 days with incremental 
backup in between, and test all backups on a recover server every 
day. As pointed, we have no single database failure that required to 
use the replication server or the backup server, but we will not 
lower the attention.

Which means you tested your backups?



Recover and run our main app on it.


Regards,


Edson





Regards,


Edson






Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

-- Mensagem original--
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org 
<mailto:pgsql-general@lists.postgresql.org>;

*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL 
traffic goes on 10 files/day or more. I have written an 
article, not yet publised, on a comparison on the 3 most known 
solutions. Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql 





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



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



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

Re: POSTGRES/MYSQL

[Edson Carlos Ericksson Richter]

Em 11/03/2019 16:20, Gavin Flower escreveu:

On 12/03/2019 05:35, Michael Nolan wrote:
[...]
 MySQL is better at isolating users from each other and requires less 
expertise to administer.


[...]

I keep reading that MySQL is easier to administer, but never seen any 
evidence of that.  And in my very limited experience of both, I've 
found PostgreSQL easier to set up & administer.



If you allow me an apart, "easy to administer" must means also that we 
have no referencial integrity hell - which MySQL doesn't guarantee. I 
had problems in past even using InnoDB, which is supposed to work... 
Easy to adminiter also means not having to deal with internal tables 
whenever MySQL decides not accepting our administrator passwords anymore...


So, IMHO, easy to administer is a system that don't drive me crazy, and 
_for me_ this system is PostgreSQL - at least in my very special case 
(which is reach old age without becoming nuts).


;-)


Regards,


Edson

Re: Retirar e-mail da lista

[Edson Carlos Ericksson Richter]

Em 14/03/2019 09:05, Angélica Barcellos escreveu:
Bom dia. Recebo constantemente os e-mails,tem como retirar o meu 
e-mail dessa lista. Não quero receber mais nada. OBRIGADO


Angélica Barcelos Cardoso



Hello, Angelica.

I'm not the list administrator, but I believe that you will have to run 
the unsubscribe process yourself.


Perhaps, being a english language list, is more probable that you get 
help if you ask your questions in english...


I hope being of kind help.

--

Oi, Angelica.

Eu não sou administrador desta lista, mas acredito que você precise 
fazer você mesmo o processo de "unsubscribe".


Além do mais, por ser uma lista em inglês, é provável que você obtenha 
auxílio se fizer a pergunta em inglês...


Espero ter ajudado.

--

Atenciosamente,

Edson

Re: Database issues when adding GUI

[Edson Carlos Ericksson Richter]

Em 07/06/2021 13:08, Alan Hodgson escreveu:

On Mon, 2021-06-07 at 09:03 -0700, Rich Shepard wrote:
The problem source is postgres telling me it cannot connect to the 
database

but I can do so directly using psql:

$ psql --host salmo --user rshepard --dbname bustrac
psql: error: could not connect to server: could not connect to 
server: Connection refused

Is the server running on host "salmo" (127.0.1.1) and accepting
TCP/IP connections on port 5432?



Are you sure it should be 127.0.1.1, not 127.0.0.1? AFAIK, localhost 
should be 127.0.0.1


May be an issue in /etc/hosts for "salmo" host?


Just my 2c,


Edson

Re: Issue with PSQL JDBC Driver Null Pointer

[Edson Carlos Ericksson Richter]

I would do


public String getTypeForAlias(String alias) {

	if (alias==null) return null; // this shall solve NPE on 
alias.toLowerCase()


String type = TYPE_ALIASES.get(alias);

if (type != null) {

return type;

}

type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;

if (type == null) {

type = alias;

}

//populate for future use

TYPE_ALIASES.put(alias, type);

return type;

}




Em 29/07/2021 16:44, Bhavesh Mistry escreveu:

Hi Dave,

It still does not address the NPE issue.  If an alias is NULL. What 
should be the behavior?



public String getTypeForAlias(String alias) {

String type = TYPE_ALIASES.get(alias);

if (type != null) {

return type;

}

type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;

if (type == null) {

type = alias;

}

//populate for future use

TYPE_ALIASES.put(alias, type);

return type;

}

Thanks,

Bhavesh

On Thu, Jul 29, 2021 at 8:44 AM Dave Cramer 
 wrote:




On Thu, 29 Jul 2021 at 11:04, Bhavesh Mistry
mailto:bhavesh.mistr...@gmail.com>>
wrote:

Hello,

I am getting an NPE exception in the JDBC driver 42.2.23.

When the alias is null, NPE results.  Is it possible to handle
null value ? I have filled a bug against RCA
https://github.com/vladmihalcea/hibernate-types/issues/335
.
But, I thought that PSQL Driver can handle it more gracefully.

|Caused by: java.lang.NullPointerException: null at

org.postgresql.jdbc.TypeInfoCache.getTypeForAlias(TypeInfoCache.java:675)
at
org.postgresql.jdbc.TypeInfoCache.getPGArrayType(TypeInfoCache.java:519)
at
org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1375)
at
org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1399)
at

org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at

org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at

org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at

com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor$1.doBind(ArraySqlTypeDescriptor.java:36)
at
org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
at

org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
at

org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
at

org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
at

org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2995)
at

org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3292)
at

org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3828)
at

org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
at

org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at

org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
at
java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
at

org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
at

org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
at

org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
at

org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
at
org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
at
org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
at

org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
at

org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
at

org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
at

org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
at

org.hibernate.resource.transaction.backend.jdb

Re: The tragedy of SQL

[Edson Carlos Ericksson Richter]

Em 15/09/2021 21:55, Adrian Klaver escreveu:

On 9/15/21 5:30 PM, FWS Neil wrote:



On Sep 15, 2021, at 2:44 PM, Merlin Moncure > wrote:





I think you ought to recognize that many people on this list make
money directly from managing that complexity :-).



I did not intend to disparage anyone.  People, including myself, make 
money when they provide value and there is certainly value here.


But, I am not sure I understand your inference.  Are you saying (and 
I am not implying you are) that PostgreSQL does not progress in line 
with the original SQL goals of simplifying data access because people 
are making money off of the current complexity?




I'm going to say Merlin was being one part sarcastic, one part saying 
people may not want to bite the hand that feeds them.


As to SQL, if Postgres wants to maintain it's goal of hewing to the 
SQL standard then what progress it came make is determined by the SQL 
standards committee.



I love the fact that PostgreSQL keep in sync with SQL standard. I love 
SQL as it is (and I came from a world with Cobol, Clipper and Dbase, and 
other 4GL etc tools).


Also, I teach SQL to my junior employees, and they love it once they 
fully compreehends - even stop defending the NoSQL hype.


At other side, I would see no objection if someone else would implement 
another language on top of PostgreSQL engine.



Just my 2c.

Regards,

Edson

Re: To all who wish to unsubscribe

[Edson Carlos Ericksson Richter]

Em 20/11/2017 17:45, Joshua D. Drake escreveu:

On 11/20/2017 11:40 AM, Magnus Hagander wrote:


    https://wiki.postgresql.org/wiki/PGLister_Announce


    Those looking to unsubscribe should also read that page.  
Sending

    "unsubscribe" messages to the list will not accomplish anything
    except to annoy the rest of the list membership.


    This is true but I would suggest it was a flaw in the migration
    not the user wondering why they are currently getting spammed
    because their filters no longer work.


So do you have any suggestions for actually fixing that? Given that 
we have more lists to migrate, if you can figure out a way to make 
those changes without peoples filters not matching, we'd be happy to 
hear it..


I was thinking about that. I was actually surprised at how many people 
this affected. It only affected one filter for me so it wasn't that 
big of a deal. One thing I would note is that there is no longer a 
footer that tells people what to do if they want to unsubscribe. 
Perhaps one thing that could be done is a header (for a temporary time 
period) that says:


The mailing list software of Postgresql.org has changed. Please see 
this page on instructions on how to manage your subscription and filters.


And then after the temporary time period that becomes a footer?

Thanks,

JD

I've been affected also, in a minor way, because I used to receive 
digest  2 or 3 times a day, and now I'm receiving every e-mail again.

This is, at least, annoying.
would be better to keep the user options when migrating from one 
software to another.


Regards,

Edson

Re: To all who wish to unsubscribe

[Edson Carlos Ericksson Richter]

Em 20/11/2017 18:07, Joshua D. Drake escreveu:

On 11/20/2017 12:03 PM, Tom Lane wrote:

Unfortunately, the removal of the footer is a feature not a bug.
In order to be DKIM-compatible and thus help avoid becoming classified
as spammers, we can't mangle message content anymore, just like we
can't mangle the Subject: line.


Ugh, o.k.

In principle, the List-Unsubscribe: headers that are now included in
mailing list headers allow MUAs to offer convenient unsubscribe
buttons.  Not sure how many of the people who are complaining use
mail agents that don't handle that.


I use Thunderbird which I imagine most people on the lists are using. 
I can't find where these would work to unsubscribe.


Well this is a pain for everyone it seems.

JD

Seems that Thunderbird (latest stable version) doesn't understand the 
unsubscribe header.

Or, perhaps, it is wrong.

Regards,

Edson

Re: How to know if a database has changed

[Edson Carlos Ericksson Richter]

Em 12/12/2017 10:14, marcelo escreveu:

Hi Sam

You are right, and here are the reason behind my question: The server 
where postgres will be installed is not on 24/7. It turns on in the 
morning and goes off at the end of the day. The idea is that, as part 
of the shutdown process, a local backup is made. The next day, that 
backup will be copied to the cloud.
In order not to lengthen the shutdown process, we are trying to limit 
pg_dump to the databases that have had some change, not so much in 
their schema as in their data.
Of course, to add a trigger for every table and CUD operation on every 
database is not an option.


On 11/12/17 23:23, Sam Gendler wrote:
I think there's a more useful question, which is why do you want to 
do this?  If it is just about conditional backups, surely the cost of 
backup storage is low enough, even in S3 or the like, that a 
duplicate backup is an afterthought from a cost perspective? Before 
you start jumping through hoops to make your backups conditional, I'd 
first do some analysis and figure out what the real cost of the thing 
I'm trying to avoid actually is, since my guess is that you are deep 
into a premature optimization 
 here, where either the 
cost of the duplicate backup isn't consequential or the frequency of 
duplicate backups is effectively 0.  It would always be possible to 
run some kind of checksum on the backup and skip storing it if it 
matches the previous backup's checksum if you decide that there truly 
is value in conditionally backing up the db.  Sure, that would result 
in dumping a db that doesn't need to be dumped, but if your write 
transaction rate is so low that backups end up being duplicates on a 
regular basis, then surely you can afford the cost of a pg_dump 
without any significant impact on performance?


On Mon, Dec 11, 2017 at 10:49 AM, Andreas Kretschmer 
mailto:andr...@a-kretschmer.de>> wrote:




Am 11.12.2017 um 18:26 schrieb Andreas Kretschmer:

it's just a rough idea...


... and not perfect, because you can't capture ddl in this way.



Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.

www.2ndQuadrant.com 







Hi, there are plenty of options for secure and optimized bacup solutions 
for your scenario.
Since you want to backup to the cloud, why not to use pgBarman with 
diferential backup + log (wal) shipping?
It is perfect, you will have zero downtime, and you can shutdown your 
database anytime you want.
In my experience, diferential backups (with rsync and symlinks) provide 
excellent performance and reduced storage (avoid duplications), and 
works perfectly well, as it also provides automatic rotation for old 
backups (you define the rules from a set of options).

I've been using pgBarman since 1.4, and I'm very satisfied with it.

Just my 2c,

Edson Richter

Re: PostgreSQL needs percentage function

[Edson Carlos Ericksson Richter]

Em 18/12/2017 14:28, Michael Nolan escreveu:



On Mon, Dec 18, 2017 at 6:23 AM, Nick Dro > wrote:


Hi,
Why PostgreSQL doesn't have build-in function to calculate
percentage? somthing like percent(number,%
for example:
select percent(100,1) will calculate 1% of 100 = 1
select percent(25,20) will calculate 20% of 25 = 5
Seems like a nice addition to the math functions list:
https://www.postgresql.org/docs/9.5/static/functions-math.html

This is veryhelpull function, many uses percentage calculation in
thier work and it will simplify the process. Percentage
calculation is considered a basic math operation and I think
postgresql should support it as a build-in function.
Seems like easy to implment isn't it?


It's a bit trickier than that, because you';ll have to deal with 
integers, real, double precision, etc.  You may also want to deal with 
null values.  I found it more useful to write a function that displays 
X as a percentage of Y, rounded to 1 digit to the right of the decimal 
point.

--
Mike Nolan


I believe that having a built-in function for percentage is not a good 
idea. Is just like having a function to calculate fractions... Someone 
would like to have percentages as 0..1, others would like to have it 
multiplied by 100. How to deal with integers? And so on (as others 
already stated here).


But, is it possible to have a aggregate function that calculates de 
percent from the sum (and/or count) total (as a value from 0 ... 1) for 
numeric (or double)?


Looking really superficially seems to me that would be a great addition 
to the aggreggates (and is not just a trivial div/multiply op)



Edson

Re: PostgreSQL needs percentage function

[Edson Carlos Ericksson Richter]

Em 18/12/2017 15:01, David G. Johnston escreveu:
On Mon, Dec 18, 2017 at 9:56 AM, Edson Carlos Ericksson Richter 
mailto:rich...@simkorp.com.br>>wrote:


But, is it possible to have a aggregate function that calculates
de percent from the sum (and/or count) total (as a value from 0
... 1) for numeric (or double)?


​Do you mean:

SELECT id, val, val / (sum(val) OVER ())
FROM vals;​

​David J.​



I've never thought about Window Functions that way.
Thanks, it is enligthning.

:-)

Regards,

Edson.

Stand by server (9.6.6) with corrupt file

[Edson Carlos Ericksson Richter]

Environment: PostgreSQL 9.6.6 installed from yum repository. Oracle 
Linux 7 EL x64. Dell servers with Raid 5 (hw).



I was testing our database backup system (based on pgBarman), and 
discovered that one base file is corrupt in our standby database server. 
The file is OK in master server, but has 0 bytes in size in standby server.


Looking master and standby servers there is no indication that the 
problem exists - _replication is running fine_.



Evidences:

On master server:

[root@server2 1106839]# find 6302536 -exec stat \{\} \;
  File: “6302536”
  Size: 16793600 Blocks: 32800      IO Block: 4096   arquivo comum
Device: f902h/63746d Inode: 10618465    Links: 1
Access: (0600/-rw---)  Uid: (   26/postgres)   Gid: (   26/postgres)
Access: 2017-12-08 21:35:38.670841051 -0200
Modify: 2017-12-21 22:51:40.706074439 -0200
Change: 2017-12-21 22:51:40.706074439 -0200
 Birth: -


On standby server:

[root@server3 1106839]# find 6302536 -exec stat \{\} \;
  File: “6302536”
  Size: 0  Blocks: 0          IO Block: 4096   arquivo comum vazio
Device: f901h/63745d Inode: 391519656   Links: 1
Access: (0600/-rw---)  Uid: (   26/postgres)   Gid: (   26/postgres)
Access: 2017-12-09 15:50:47.469135640 -0200
Modify: 2017-12-09 15:50:47.469135640 -0200
Change: 2017-12-09 15:50:47.469135640 -0200
 Birth: -


After long investigation, I discovered that if I execute a query on 
standby server:


< 2017-12-22 11:20:22.417 -02 > ERROR:  could not read block 0 in file 
"base/1106839/6302536": read only 0 of 8192 bytes

< 2017-12-22 11:20:22.417 -02 > STATEMENT:  SELECT *
  FROM MY_FAIR_LARGE_TABLE t1

 LEFT OUTER JOIN MY_FAIR_LARGE_SUBTABLE t0 ON (t0.the_id = t1.ID)
   WHERE (((t1.COMPANY_ID = 2)
 AND t1.OTHERCOMPANY LIKE '20147617%')
 AND (t1.TEST_FLAG = 0))
    ORDER BY t1.DUE_DATE LIMIT 1000 OFFSET 0


Very same query on server works fine.

And there is no replication error - everything is in sync between these 
two servers (I know, I'm begin to be repetitive).


I've about 30 servers with same setup, and this only has this flaw. The 
only difference is that this database is about 3 times larger than the 
others (about 90Gb in size).


Server and slave have 23ms of network lag - which seems not be a problem 
for the other databases in the same server.



Any advice?

--

*Edson Carlos Ericksson Richter*
/SimKorp Ltda/
Fone:   (51) 3366-7964
Embedded Image
/"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho 
original"/

- Albert Einstein

Re: Stand by server (9.6.6) with corrupt file

[Edson Carlos Ericksson Richter]

No, it is a normal table.


*Edson Carlos Ericksson Richter*
/SimKorp Ltda/
Fone:   (51) 3366-7964
Embedded Image
/"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho 
original"/

- Albert Einstein


Em 22/12/2017 11:42, Stephen Frost escreveu:

Greetings,

* Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote:

I was testing our database backup system (based on pgBarman), and
discovered that one base file is corrupt in our standby database
server. The file is OK in master server, but has 0 bytes in size in
standby server.

[...]

Any advice?

Have you checked if it's an unlogged table?

Thanks!

Stephen

Re: Stand by server (9.6.6) with corrupt file

[Edson Carlos Ericksson Richter]

I've used rsync method as described in PostgreSQL wiki.

Database is quite large, and pg_basebackup fail after running for 
several hours over a 5Mbps link.


To me, rsync appears to be more reliable over unstable links.

Nevertheless, if some data has been corrupted during copy across the 
wire, shall not the standby start?



Regards,


*Edson Carlos Ericksson Richter*
/SimKorp Ltda/
Fone:   (51) 3366-7964
Embedded Image
/"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho 
original"/

- Albert Einstein


Em 23/12/2017 09:58, Martin Marques escreveu:

El 22/12/17 a las 10:29, Edson Carlos Ericksson Richter escribió:

Environment: PostgreSQL 9.6.6 installed from yum repository. Oracle
Linux 7 EL x64. Dell servers with Raid 5 (hw).


I was testing our database backup system (based on pgBarman), and
discovered that one base file is corrupt in our standby database server.
The file is OK in master server, but has 0 bytes in size in standby server.

How was the standby built? Did you use a recovered backup from barman or
did you clone from the master with some tool. An if it's the former,
which exact process did you use? (Using pg_basebackup would be the
safest way of cloning)

Regards,

Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Recently I had a problem with a base file with size 0 in a standby server.

This raised one question: does PostgreSQL (9.6.6) check base integrity 
at startup?


At least if there are 0 byte size files in base dir? Or CRC? Something?


Regards,

--

*Edson Carlos Ericksson Richter*
/SimKorp Ltda/
Fone:   (51) 3366-7964
Embedded Image
/"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho 
original"/

- Albert Einstein

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 12:25, Pavel Stehule escreveu:



2017-12-26 14:44 GMT+01:00 Martin Marques 
mailto:martin.marq...@2ndquadrant.com>>:


El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> Recently I had a problem with a base file with size 0 in a
standby server.
>
> This raised one question: does PostgreSQL (9.6.6) check base
integrity
> at startup?
>
> At least if there are 0 byte size files in base dir? Or CRC?
Something?

Yes it has CRC check, but only if you initialize the cluster with
--data-checksums, and there's a price to pay in performance.


It has CRC check, but it is used in runtime - when data are necessary

So Postgres usually check nothing on start - few system tables and indexes

Regards

Pavel


--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services




Any tips to make database server don't start if corrupt?
If I can change the startup script to make some checks before 
effectively starting the database, what would be the recommendations?


One that seems obvious to me are empty data files (something like "find 
-size 0 $PG_DATA/base")...
But I'm sure that more experienced PostgreSQL DBA would have more tests 
to check before startup.


Thanks,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 13:40, Pavel Stehule escreveu:



2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter 
mailto:rich...@simkorp.com.br>>:


Em 26/12/2017 12:25, Pavel Stehule escreveu:



2017-12-26 14:44 GMT+01:00 Martin Marques
mailto:martin.marq...@2ndquadrant.com>>:

El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> Recently I had a problem with a base file with size 0 in a
standby server.
>
> This raised one question: does PostgreSQL (9.6.6) check
base integrity
> at startup?
>
> At least if there are 0 byte size files in base dir? Or
CRC? Something?

Yes it has CRC check, but only if you initialize the cluster with
--data-checksums, and there's a price to pay in performance.


It has CRC check, but it is used in runtime - when data are
necessary

So Postgres usually check nothing on start - few system tables
and indexes

Regards

Pavel


--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services




Any tips to make database server don't start if corrupt?
If I can change the startup script to make some checks before
effectively starting the database, what would be the recommendations?

One that seems obvious to me are empty data files (something like
"find -size 0 $PG_DATA/base")...
But I'm sure that more experienced PostgreSQL DBA would have more
tests to check before startup.


I don't think so anybody does it. Reading 1TB database needs more then 
few hours.


Regards



Thanks,

Edson


I'm rebuilding the standby server for two days already, with 23% of 
completion status...
If lost the database and backups because of that failure, it would be a 
giant disaster.
Few hours checking integrity would be acceptable... Specially if I can 
run it on standby only.


Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 13:57, Pavel Stehule escreveu:



2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter 
mailto:rich...@simkorp.com.br>>:


Em 26/12/2017 13:40, Pavel Stehule escreveu:



2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter
mailto:rich...@simkorp.com.br>>:

Em 26/12/2017 12:25, Pavel Stehule escreveu:



2017-12-26 14:44 GMT+01:00 Martin Marques
mailto:martin.marq...@2ndquadrant.com>>:

El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter
escribió:
> Recently I had a problem with a base file with size 0
in a standby server.
>
> This raised one question: does PostgreSQL (9.6.6)
check base integrity
> at startup?
>
> At least if there are 0 byte size files in base dir?
Or CRC? Something?

Yes it has CRC check, but only if you initialize the
cluster with
--data-checksums, and there's a price to pay in performance.


It has CRC check, but it is used in runtime - when data are
necessary

So Postgres usually check nothing on start - few system
tables and indexes

Regards

Pavel


--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services




Any tips to make database server don't start if corrupt?
If I can change the startup script to make some checks before
effectively starting the database, what would be the
recommendations?

One that seems obvious to me are empty data files (something
like "find -size 0 $PG_DATA/base")...
But I'm sure that more experienced PostgreSQL DBA would have
more tests to check before startup.


I don't think so anybody does it. Reading 1TB database needs more
then few hours.

Regards



Thanks,

Edson



I'm rebuilding the standby server for two days already, with 23%
of completion status...
If lost the database and backups because of that failure, it would
be a giant disaster.
Few hours checking integrity would be acceptable... Specially if I
can run it on standby only.


very simple check

pgdumpall > /dev/null

but this doesn't check indexes.

Regards

Pavel




I'll check it and make some tests.
Thanks!

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 15:13, Alvaro Herrera escreveu:

Edson Carlos Ericksson Richter wrote:


I'm rebuilding the standby server for two days already, with 23% of
completion status...

So how do you build your standbys, exactly?  Maybe there's a bug in your
procedure, rather than a bug in the software.

Usually, or by "pg_basebackup" or by "rsync" as described in PostgreSQL 
Wiki.

Right now, I'm doing via pg_basebackup.
Nothing extraordinary.

When using rsync:

rsync -e "ssh -2 -C -p slave_ssh_port" --progress --partial -az 
/pgsql/9.6/master_data_folder/* 
superuser_name@$slave_host:/home/pgsql/9.6/slave_data_folder/ --exclude 
postmaster.pid --exclude postgresql.conf --exclude pg_log


When using pg_basebackup:

/usr/pgsql-9.6/bin/pg_basebackup -h master_host -p master_port -D 
/home/pgsql/9.6/master_data_folder -R -S slot_name -X stream -P -d " 
user=superuser_name password=superuser_pass sslmode=require "



Of course, this is inside a bash script with variables at right places 
to make script generic as needed to make it works with dozens of 
databases...


Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 20:42, Martin Marques escreveu:

El 26/12/17 a las 14:46, Edson Carlos Ericksson Richter escribió:

Usually, or by "pg_basebackup" or by "rsync" as described in PostgreSQL
Wiki.
Right now, I'm doing via pg_basebackup.
Nothing extraordinary.

When using rsync:

rsync -e "ssh -2 -C -p slave_ssh_port" --progress --partial -az
/pgsql/9.6/master_data_folder/*
superuser_name@$slave_host:/home/pgsql/9.6/slave_data_folder/ --exclude
postmaster.pid --exclude postgresql.conf --exclude pg_log

You don't say so anywhere, but I suspect you run the rsync between a
pg_start_backup() and pg_stop_backup(), right?

That is the way it's described in the wiki.

Regards,

Yes, sure. When using wiki, I'm following all instructions. Process 
revisited many times do check if something changed (since 9.0 days :-) )


Right now, I do prefer to use pg_basebackup instead - but sometimes 
(when database is just too large), rsync seems more reliable (maybe it 
is the cause of the problem).


Anyway, instead digging into rsync functionality (or bugs - I doubt, but 
who knows?), I do prefer to have a script I can run to check if there is 
obvious failures in standby servers.


Looking for empty files would be a start point.
I'm learning from experienced people from the list that are other points 
I would like to check as well.


Regards,

Edson.

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 20:11, rob stone escreveu:

Hello,


On Tue, 2017-12-26 at 18:58 -0300, Alvaro Herrera wrote:Hello,

David Steele wrote:


pgBackRest will validate all page checksums (including indexes,
etc.) in the
cluster during backup.  Full backups check everything,
incr/differential
backups check only the files that have changed.

If a table or index file is of zero length when backed up, as in the
described case, nothing will be checked, right?  I mean, there is
nothing externally indicating that the file ought to be of a
different
size.  Am I wrong?  So Edson's situation here would not raise any red
flags.



Could the following occur:-
1) Your app. issues a BEGIN followed by an INSERT.
2) Postgres decides to open a new file in order to store the new row.
3) Your app. then does a ROLLBACK.

Wouldn't that leave you with a zero length file on disk?

There's no reason for Postgres to delete the file just because a
rollback was issued. All it has to do is clear the buffer in memory.

My 2 cents.

Rob




You are right, in several databases I've looked there are many files 
with 0 byte size in ./base folder.

This is not a good quest.

Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 27/12/2017 15:02, Stephen Frost escreveu:

Alvaro,

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:

Stephen Frost wrote:


* Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote:

Anyway, instead digging into rsync functionality (or bugs - I doubt,
but who knows?), I do prefer to have a script I can run to check if
there is obvious failures in standby servers.

As mentioned, zero-byte files can be perfectly valid.  PostgreSQL does
have page-level CRCs, if you initialized your database with them (which
I would strongly recommend).

Page-level checksums would not detect the problem being complained in
this thread, however.

It's entirely unclear to me what the problem being complained about in
this thread actually is.  The complaint so far was about zero-byte
files, but those are entirely valid, so that isn't a problem that anyone
can solve..

Given the thread subject, if someone actually wanted to do a database
integrity check before startup, they could use pgBackRest to perform a
backup with a CRC-enabled database and at least verify that all of the
checksums are valid.

We could possibly look into adding some set of additional checks for
files which can't actually be zero-byte, perhaps..  I know we have some
other one-off checks already.

Thanks!

Stephen


Actually, the problem is:

Master => Slave => Backup

In the master server everything is fine.
But at some point in time, slave became corrupt (one of the base files 
are zero size where it should be 16Mb in size), and IMHO a "red alert" 
should arise - Slave server shall not even startup at all.


Since backups are taken from slave server, all backups are also corrupt.

I've detected the problem just because I've restored a backup (excellent 
practice perhaps - nobody should take backups if not testing it with the 
restore procedure).


In slave server there is no indication that the database is corrupt (not 
in logs, it starts normally and show it is applying stream changes 
regularly).


So that is the point: how to detect that a database is corrupt so 
cluster doesn't even start...


Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 28/12/2017 10:16, Stephen Frost escreveu:

Alvaro,

* Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote:

For context: this was first reported in the Barman forum here:
https://groups.google.com/forum/#!msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ
They are using Barman for the backups.

A, I see.  I wasn't aware of that history.


Stephen Frost wrote:


But at some point in time, slave became corrupt (one of the base
files are zero size where it should be 16Mb in size), and IMHO a
"red alert" should arise - Slave server shall not even startup at
all.

How do you know it should be 16Mb in size...?  That sounds like you're
describing a WAL file, but you should be archiving your WAL files during
a backup, not just using whatever is in pg_xlog/pg_wal..

It's not a WAL file -- it's a file backing a table.

Interesting.


Since backups are taken from slave server, all backups are also corrupt.

If you aren't following the appropriate process to perform a backup
then, yes, you're going to end up with corrupt and useless/bad backups.

A few guys went over the backup-taking protocol upthread already.

But anyway the backup tool is a moot point.  The problem doesn't
originate in the backup -- it originates in the standby, from where the
backup is taken.  The file can be seen as size 0 in the standby.
Edson's question is: why wasn't the problem detected in the standby?
It seems a valid question to me, to which we currently we don't have any
good answer.

The last message on that thread seems pretty clear to me- the comment is
"I think this is a failure in standby build."  It's not clear what that
failure was but I agree it doesn't appear related to the backup tool
(the comment there is "I'm using rsync"), or, really, PostgreSQL at all
(a failure during the build of the replica isn't something we're
necessairly going to pick up on..).

As discussed on this thread, zero-byte files are entirely valid to
appear in the PostgreSQL data directory.

To try and dig into what happened, I'd probably look at what forks there
are of that relation, the entry in pg_class, and try to figure out how
it is that replication isn't complaining when the file on the primary
appeared to be modified well after the last modify timestamp on the
replica.  If it's possible to replica this into a test environment,
maybe even do a no-op update of a row of that table and see what happens
with replication.  One thing I wonder is if this table used to be
unlogged and it was later turned into a logged table but something
didn't quite happen correctly with that.  I'd also suggest looking for
other file size mismatches between the primary and the replica.

Thanks!

Stephen
The table was never unlogged. From very beginning, it was always logged. 
I've dozens of databases with exactly same setup - and right now, I'm 
rebuilding the slave server. Instead of investigating something probably 
I will not find the cause, I would like to have a alert for the future.


Would be possible to include in future versions:
1) After start standby, standby run all WAL files until it is 
synchronized with master (current behavior)
3) Before getting into "accept read only queries", check if all base 
files have same size as master server (new behavior). In case something 
is different, throw an error and stop database startup?

4) Then start "accept read only queries" (current behavior)
???

Thanks,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 28/12/2017 16:06, Brent Wood escreveu:

Some thoughts

A tool to calculate a checksum of sorts based on the table (file) 
content would provide a better surety of duplication than simply 
checking file size - like differently vacuumed tables in each copy 
could have the same content but be different file sizes.


Something like these could be adapted to compare database content by 
filesystem checks rather than db queries. Following tablespaces, etc 
as well.


http://www.commandlinefu.com/commands/view/3555/find-duplicate-files-based-on-size-first-then-md5-hash
or other similar tools

Yes, there is some overhead, especially for large databases but it 
would be worth that to robustly ensure genuine and complete duplication.


I do wonder though - given the order of records in a table (file) is 
not necessarily identical (or is it?) event this may be problematic. 
Perhaps a checksum based on the result of a query output ordered by 
primary key could work?


Brent Wood


*From:* Edson Carlos Ericksson Richter 
*To:* pgsql-general@lists.postgresql.org
*Sent:* Friday, December 29, 2017 6:47 AM
*Subject:* Re: Does PostgreSQL check database integrity at startup?

Em 28/12/2017 10:16, Stephen Frost escreveu:
> Alvaro,
>
> * Alvaro Herrera (alvhe...@alvh.no-ip.org 
<mailto:alvhe...@alvh.no-ip.org>) wrote:

>> For context: this was first reported in the Barman forum here:
>> 
https://groups.google.com/forum/#!msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ 
<https://groups.google.com/forum/#%21msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ>

>> They are using Barman for the backups.
> A, I see.  I wasn't aware of that history.
>
>> Stephen Frost wrote:
>>
>>>> But at some point in time, slave became corrupt (one of the base
>>>> files are zero size where it should be 16Mb in size), and IMHO a
>>>> "red alert" should arise - Slave server shall not even startup at
>>>> all.
>>> How do you know it should be 16Mb in size...?  That sounds like you're
>>> describing a WAL file, but you should be archiving your WAL files 
during

>>> a backup, not just using whatever is in pg_xlog/pg_wal..
>> It's not a WAL file -- it's a file backing a table.
> Interesting.
>
>>>> Since backups are taken from slave server, all backups are also 
corrupt.

>>> If you aren't following the appropriate process to perform a backup
>>> then, yes, you're going to end up with corrupt and useless/bad 
backups.

>> A few guys went over the backup-taking protocol upthread already.
>>
>> But anyway the backup tool is a moot point. The problem doesn't
>> originate in the backup -- it originates in the standby, from where the
>> backup is taken.  The file can be seen as size 0 in the standby.
>> Edson's question is: why wasn't the problem detected in the standby?
>> It seems a valid question to me, to which we currently we don't 
have any

>> good answer.
> The last message on that thread seems pretty clear to me- the comment is
> "I think this is a failure in standby build." It's not clear what that
> failure was but I agree it doesn't appear related to the backup tool
> (the comment there is "I'm using rsync"), or, really, PostgreSQL at all
> (a failure during the build of the replica isn't something we're
> necessairly going to pick up on..).
>
> As discussed on this thread, zero-byte files are entirely valid to
> appear in the PostgreSQL data directory.
>
> To try and dig into what happened, I'd probably look at what forks there
> are of that relation, the entry in pg_class, and try to figure out how
> it is that replication isn't complaining when the file on the primary
> appeared to be modified well after the last modify timestamp on the
> replica.  If it's possible to replica this into a test environment,
> maybe even do a no-op update of a row of that table and see what happens
> with replication.  One thing I wonder is if this table used to be
> unlogged and it was later turned into a logged table but something
> didn't quite happen correctly with that.  I'd also suggest looking for
> other file size mismatches between the primary and the replica.
>
> Thanks!
>
> Stephen
The table was never unlogged. From very beginning, it was always logged.
I've dozens of databases with exactly same setup - and right now, I'm
rebuilding the slave server. Instead of investigating something probably
I will not find the cause, I would like to have a alert for the future.

Would be possible to include in future versions:
1) After start standby, standby run all WAL files until it is
synchronized with master (current behavior)
3) Before getting into "accept read only queries", check if all base
files have same size as master server (new behavior). In case something
is different, throw an error and stop database startup?
4) Then start "accept read only queries" (current behavior)
???

Thanks,


Edson


Thanks, Brent. I'll think about it.

Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 28/12/2017 16:26, Stephen Frost escreveu:

Greetings Brent,

* Brent Wood (pcr...@yahoo.com) wrote:

A tool to calculate a checksum of sorts based on the table (file) content would 
provide a better surety of duplication than simply checking file size - like 
differently vacuumed tables in each copy could have the same content but be 
different file sizes.

PG has support for checksums and there are tools out there to validate
that the checksum is correct for all pages which have one, but that
wouldn't help in this case because the file is zero'd out (and a zero'd
out file is actually a valid file in a PG data directory).

Also, the files on the primary and the replica actually can be different
when looked at with a complete-file checksum due to hint bits being set
differently (at least, possibly other ways too).  That doesn't make them
invalid or incorrect though.

Rather than trying to compare a running primary to a running replica,
such a check to verify that the files backed up during a backup have the
same checksum as the files being restored from that backup can be done,
and that *is* done in at least some of the PG backup tools already
(pgBackRest has an independent manifest that it stores for each backup
which contains the checksum of each file as-backed-up, and it verifies
that checksum when performing a restore to make sure that the backed up
file wasn't corrupted in place, other tools hopefully have similar).


I do wonder though - given the order of records in a table (file) is not 
necessarily identical (or is it?) event this may be problematic. Perhaps a 
checksum based on the result of a query output ordered by primary key could 
work?

The order of records in a *file* should be the same in the heap on the
primary as they are on the replica, but that doesn't mean the contents
of those files will be exactly the same (as mentioned above, hint bits
can differ).  We used to have cases where the indexes could also be
different, but I believe that was changed so they should match.

I've used the approach of doing a checksum across the results of an
ordered query to compare between systems and that generally does work,
but it's a bit tricky if you're trying to compare a table that's heavily
modified- you need to determine the point in the WAL stream that you're
at on the primary when you run the query and then replay the replica to
that point in the WAL and then run the query on the replica, otherwise
you could end up with differences that are just because of the ongoing
transactions being run to update the table.

Thanks!

Stephen


Stephen, thanks for you detailed reasoning on why this would not be so 
simple.


I'll take all of that in consideration. I hope I find something that 
gives a bit more confidence that my replicas are enought reliable. At 
this moment, I just don't trust them.


Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 29/12/2017 22:14, Jan Wieck escreveu:



On Thu, Dec 28, 2017 at 1:26 PM, Stephen Frost > wrote:


Greetings Brent,

* Brent Wood (pcr...@yahoo.com ) wrote:
> A tool to calculate a checksum of sorts based on the table
(file) content would provide a better surety of duplication than
simply checking file size - like differently vacuumed tables in
each copy could have the same content but be different file sizes.

PG has support for checksums and there are tools out there to validate
that the checksum is correct for all pages which have one, but that
wouldn't help in this case because the file is zero'd out (and a
zero'd
out file is actually a valid file in a PG data directory).

Also, the files on the primary and the replica actually can be
different
when looked at with a complete-file checksum due to hint bits
being set
differently (at least, possibly other ways too).  That doesn't
make them
invalid or incorrect though.


In addition to what Stephen and everyone else said, it is impossible 
to get a valid snapshot of the whole "file" on a running server 
without locking the relation and reading it through the PostgreSQL 
buffer cache. On data files such as heap and index, PostgreSQL does 
extensive write caching. Preventing data loss from write caching is a 
primary purpose of WAL. Write caching in the application (PostgreSQL 
in this case) prevents the OS from actually knowing the correct 
"logical" state of the file at any given point in time. This means 
that even a LVM snapshot will not give you consistent data files of a 
running server, because the not yet written changes (in shared 
buffers) waiting for a checkpoint to force them into OS buffers won't 
be visible from outside PostgreSQL.



Regards, Jan



such a check to verify that the files backed up during a backup
have the
same checksum as the files being restored from that backup can be
done,
and that *is* done in at least some of the PG backup tools already
(pgBackRest has an independent manifest that it stores for each backup
which contains the checksum of each file as-backed-up, and it verifies
that checksum when performing a restore to make sure that the
backed up
file wasn't corrupted in place, other tools hopefully have similar).

> I do wonder though - given the order of records in a table
(file) is not necessarily identical (or is it?) event this may be
problematic. Perhaps a checksum based on the result of a query
output ordered by primary key could work?

The order of records in a *file* should be the same in the heap on the
primary as they are on the replica, but that doesn't mean the contents
of those files will be exactly the same (as mentioned above, hint bits
can differ).  We used to have cases where the indexes could also be
different, but I believe that was changed so they should match.

I've used the approach of doing a checksum across the results of an
ordered query to compare between systems and that generally does work,
but it's a bit tricky if you're trying to compare a table that's
heavily
modified- you need to determine the point in the WAL stream that
you're
at on the primary when you run the query and then replay the
replica to
that point in the WAL and then run the query on the replica, otherwise
you could end up with differences that are just because of the ongoing
transactions being run to update the table.

Thanks!

Stephen




--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info


One last question:

There should be a "catalog" that point where tables are stored in 
physical files (I think, at least, because at some point PostgreSQL need 
to know from where to read the data).


Based on information from this catalog, would I have a tool (perhaps, a 
C function) that check that data is really there?


Thanks,

Edson

Re: execute block like Firebird does

[Edson Carlos Ericksson Richter]

Em 11/02/2018 03:57, PegoraroF10 escreveu:

We are migrating our databases from Firebird to PostGres. A useful feature
Firebird has is Execute Block.
What it does is just return a record set from that dynamic SQL, just like a
PostGres function, but without creating it.
It sound like ...
execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
as
begin
   select bla, bla, bla into ...;
   select bla, bla into ...;
   suspend;
end
I know we could create a function but we have several hundred of these
blocks running, so ... it would be a huge work to do.
So, there is a way to run a dynamic sql which returns a set of records ?


Can't you use "with ... select ..."?

Like:

with qry1 as (select bla, bla, bla from xyz), qry2 as (select bla, bla 
from ...)

select * from qry1
union all
select * from qry2


?

Regards,

Edson

--

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

Custom logical replication client

[Edson Carlos Ericksson Richter]

Dear Community,

I'm starting a project where I would like to write an application to 
decode the logical replication protocol to save data into an analytics 
streaming system.


Is there a starting guide or similar project that can help me to educate 
myself on how to accomplish this task?



Thanks in advance,

ER.

Re: Custom logical replication client

[Edson Carlos Ericksson Richter]

On May 23, 2025, at 11:36, Edson Carlos Ericksson 
Richter wrote:

Dear Community,
I'm starting a project where I would like to write an application to decode the 
logical replication protocol to save data into an analytics streaming system.
Is there a starting guide or similar project that can help me to educate myself 
on how to accomplish this task?

You might start here:

https://github.com/eulerto/wal2json



Thanks!

ER