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 backup of the database, eg. a backup incremental or GTF schema files? Which commands works better at low level for background backup/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(dot)klaver(at)aklaver(dot)com