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.klaver@xxxxxxxxxxx
