Re: R: dbeaver

On 4/1/20 9:33 AM, Roberto Della Pasqua wrote:
> On 4/1/20 9:17 AM, Roberto Della Pasqua wrote:
>> Hello, please some questions about a newbie Postgresql user:
>>
>> - what is the best method to keep the data replicated (for backup purposes):
>> Can be possible to deploy a master/slave with realtime data replication and adding a storage where do a journaled
backupof the database, eg. a backup incremental or GTF schema files? Which commands works better at low level for
backgroundbackup/restore?
 
> 
> The above is book in itself.
> 
> To help more information is needed:
> 
> Postgres version(s) involved?
> LATEST VERSION 12

Alright, so you have the choice of either binary replication or the 
built in logical replication.

> 
> Size of dataset?
> 1 million of records (varchar mainly x 10 columns)
> 
> OS(es) involved?
> ClearLinux, XFS over nvmexpress

Hmm, new distro to me.

> 
> Network distance between master standby(s)?
> Same network, 1Gbps, 1msec

Did you get a chance to look over the links I sent previously to see if 
any particular setup fit your needs?

There are also third part solutions for backup/restore:

https://pgbackrest.org/

https://www.2ndquadrant.com/en/resources/barman/

https://github.com/postgrespro/pg_probackup

> 
> Does exist a common set of configurations to make the "select" op quicker?

Postgres has a planner that tries to do that work for you:

https://www.postgresql.org/docs/12/using-explain.html
https://www.postgresql.org/docs/12/planner-stats.html
https://www.postgresql.org/docs/12/planner-stats-details.html

I would try the query first and if you see issues then post here with 
the EXPLAIN ANALYZE results.

> Does PG support in-memory tables (disk persisted) as MSSQL?

That will have to be answered by someone with more knowledge of MSSQL 
then I.



-- 
Adrian Klaver
adrian.klaver@aklaver.com