Thread: dbeaver
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? - does dbeaver is a good frontend for pg administration? Roberto Della Pasqua www.dellapasqua.com
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? Size of dataset? OS(es) involved? Network distance between master standby(s)? For some high level overview: https://www.postgresql.org/docs/12/different-replication-solutions.html https://www.postgresql.org/docs/12/high-availability.html https://www.postgresql.org/docs/12/app-pgdump.html https://www.postgresql.org/docs/12/app-pgrestore.html > - does dbeaver is a good frontend for pg administration? I use the Postgres client psql for administration. I rarely use GUI frontends so I can't comment on DBeaver. > > Roberto Della Pasqua > www.dellapasqua.com > -- Adrian Klaver adrian.klaver@aklaver.com
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 Size of dataset? 1 million of records (varchar mainly x 10 columns) OS(es) involved? ClearLinux, XFS over nvmexpress Network distance between master standby(s)? Same network, 1Gbps, 1msec Does exist a common set of configurations to make the "select" op quicker? Does PG support in-memory tables (disk persisted) as MSSQL? For some high level overview: https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F12%2Fdifferent-replication-solutions.html&data=02%7C01%7C%7C2733978376ec4315c8a808d7d659978f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637213552584407018&sdata=naRegPRNIXs1zwv5I4K3o290qNQuxYLnz6Y3ocPJBn4%3D&reserved=0 https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F12%2Fhigh-availability.html&data=02%7C01%7C%7C2733978376ec4315c8a808d7d659978f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637213552584407018&sdata=cw%2Bk%2F1ifbZaOULKZpwdLRrbhjo1K7Vn58VOhx%2FkiUsk%3D&reserved=0 https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F12%2Fapp-pgdump.html&data=02%7C01%7C%7C2733978376ec4315c8a808d7d659978f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637213552584417025&sdata=vC8CAR8sCuTmhIEjNG9I7b51jLs0PwiDvu6kkCzxMWI%3D&reserved=0 https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F12%2Fapp-pgrestore.html&data=02%7C01%7C%7C2733978376ec4315c8a808d7d659978f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637213552584417025&sdata=MNyfy9r7Bp67vzoGWdHVM3nug7YMZ%2F1%2Fugyb6EnZ9vE%3D&reserved=0 > - does dbeaver is a good frontend for pg administration? I use the Postgres client psql for administration. I rarely use GUI frontends so I can't comment on DBeaver. > > Roberto Della Pasqua > https://nam02.safelinks.protection.outlook.com/?url=www.dellapasqua.co > m&data=02%7C01%7C%7C2733978376ec4315c8a808d7d659978f%7C84df9e7fe9f > 640afb435aaaaaaaaaaaa%7C1%7C0%7C637213552584417025&sdata=LPtWHn7e6 > at%2BvpLaIXEqn2yDcQuicRQd3UZ4RCu%2BZQ4%3D&reserved=0 > -- Adrian Klaver adrian.klaver@aklaver.com
On 01/04/2020 18:17, 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? No idea. > - does dbeaver is a good frontend for pg administration? That's the client that I use most often, and I love it. It has lots of useful features, such as good query completion, row coloring, virtual columns, virtual foreign keys, export to multiple formats, etc. Years ago I missed some features from pgAdmin III, but nowadays its support for PostgreSQL is excellent. And you can use the same GUI for multiple database engines, which is great. Obviously, there are tasks that you should do (or only can do) with pure SQL. But DBeaver saves you a lot of time for the most common tasks. > > Roberto Della Pasqua > www.dellapasqua.com >
> - does dbeaver is a good frontend for pg administration? It is an excellent sql client tool and I use it heavily for Redshift, SQLServer, Snowflake and PG. However it is a not dba tool in the sense that it can DBA specific details and graphs as shown in pgadmin.
negora schrieb am 01.04.2020 um 21:44: > It has lots of useful features, such as good query completion, row > coloring, virtual columns, virtual foreign keys What kind of feature is "virtual foreign keys"? Or "virtual columns" in the context of a SQL GUI tool
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