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 backup of the database, eg. a backup incremental or GTF schema files? Which commands works better at low level for background backup/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 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?
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 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
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:
- 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 backup of the database, eg. a backup incremental or GTF schema files? Which commands works better at low level for background backup/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.
Show quoted text
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 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://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
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Both features reside only in the client side, of course.<br>
</p>
<p>A virtual column allows to permanently show, in a table, a column
calculated from a JavaScript-alike expression. That expression is
based on other columns of the table and offers several
mathematical functions. I know that you can do the same with a
view, which is much more powerful, but doing this in the
client-side has some advantages in certain situations:</p>
<ol>
<li>If you have not permission to create a view.<br>
<br>
</li>
<li>If you have permission, but don't want to clutter the schema.
So it's not invasive to other users.<br>
<br>
</li>
<li>You can use this column to color the rows by a given criteria.<br>
<br>
</li>
<li>Maybe the most important: since you're viewing a table and not
a view, you can still edit the records from the GUI, as usual.<br>
</li>
</ol>
Virtual foreign keys, on the other hand, didn't seem interesting to
me... Until I had to work with a database from Microsoft Navision in
SQL Server. Navision don't use foreign keys at the database level.
So, in order to figure out the relationships among tables, I had to
add virtual FKs. That way, I had a permanent visual aid. That
feature, combined with the auto-diagrams generated by DBeaver,
allowed me to understand part of that mess. It was my salvation.<br>
<p><br>
</p>
<div class="moz-cite-prefix">On 01/04/2020 22:28, Thomas Kellerer
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:91dd694b-c19f-df70-d155-36374790499d@gmx.net">negora
schrieb am 01.04.2020 um 21:44:
<br>
<blockquote type="cite">It has lots of useful features, such as
good query completion, row
<br>
coloring, virtual columns, virtual foreign keys
<br>
</blockquote>
What kind of feature is "virtual foreign keys"?
<br>
<br>
Or "virtual columns" in the context of a SQL GUI tool
<br>
<br>
<br>
<br>
<br>
</blockquote>
</body>
</html>