Re: Incremental backup

[Peter J. Holzer]

On 2021-10-28 21:14:53 -0400, Mladen Gogala wrote:
> On 10/28/21 18:07, Andreas Joseph Krogh wrote:
> I think everybody agrees that incremental backup per database, and not
> cluster-wide, is nice, and it would be nice if PG supported it. But, given
> the way PG is architectured, having cluster-wide WALs, that's not an easy
> task to implement.
> 
> I am not advocating for the database level incremental backups, but all
> databases that have it also have cluster wide/instance wide WAL logs. Cluster
> wide WAL logs do not make database level incremental backups hard. Both Oracle
> and DB2 have database level incremental backups and both have cluster wide WAL
> (redo or logs).

I was not aware that Oracle even has something equivalent to a Postgres cluster.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

DBeaver does not show all tables in a Schema

[Shaozhong SHI]

I used a DBeaver to connect to postgres but it does not show all tables in
a schema.

Can anyone shed light on this?

Regards,

David

Re: Incremental backup

[Mladen Gogala]

On 10/29/21 03:30, Peter J. Holzer wrote:

I was not aware that Oracle even has something equivalent to a Postgres cluster.

 hp


It's called "Oracle instance".

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: DBeaver does not show all tables in a Schema

[Ron]

On 10/29/21 5:46 AM, Shaozhong SHI wrote:
I used a DBeaver to connect to postgres but it does not show all tables in 
a schema.


Can anyone shed light on this?


Permissions?

--
Angular momentum makes the world go 'round.

Re: Incremental backup

[Peter J. Holzer]

On 2021-10-29 08:38:47 -0400, Mladen Gogala wrote:
> On 10/29/21 03:30, Peter J. Holzer wrote:
> > I was not aware that Oracle even has something equivalent to a Postgres 
> > cluster.
> 
> It's called "Oracle instance".

I don't think that's equivalent. An Oracle instance is a runtime concept
(the collection of server processes on a single machine serving a single
database (the collection of files on the disk)) whereas a PostgreSQL cluster is
both a data and a runtime concept (config + data files for several
databases with some shared data + the processes serving them). Also
instance:database is n:1 while cluster to database is 1:n. Very
different.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Incremental backup

[Mladen Gogala]

On 10/29/21 08:49, Peter J. Holzer wrote:

I don't think that's equivalent. An Oracle instance is a runtime concept
(the collection of server processes on a single machine serving a single
database (the collection of files on the disk)) whereas a PostgreSQL cluster is
both a data and a runtime concept (config + data files for several
databases with some shared data + the processes serving them). Also
instance:database is n:1 while cluster to database is 1:n. Very
different.
Peter, Oracle instance manages collection of the databases and is 
ensuring recoverabilty using redo logs, which are completely analogous 
to WAL logs, if managed a bit differently. Let's not be nitpicking here. 
Oracle instance is completely analogous to Postgres cluster. If you ask 
me, the word cluster was picked to avoid the word "instance"


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: DBeaver does not show all tables in a Schema

[negora]

Are you sure that you're not applying a filter in the "Database 
Navigator" panel of DBeaver?


Sometimes, it occurs to me that I apply a filter for certain database, I 
forget to remove it, then open another database, and some or all tables 
do not appear in the navigator.



On 29/10/2021 12:46, Shaozhong SHI wrote:
I used a DBeaver to connect to postgres but it does not show all 
tables in a schema.


Can anyone shed light on this?

Regards,

David

Re: Incremental backup

[Peter J. Holzer]

On 2021-10-29 09:03:04 -0400, Mladen Gogala wrote:
> On 10/29/21 08:49, Peter J. Holzer wrote:
> > I don't think that's equivalent. An Oracle instance is a runtime concept
> > (the collection of server processes on a single machine serving a single
> > database (the collection of files on the disk)) whereas a PostgreSQL 
> > cluster is
> > both a data and a runtime concept (config + data files for several
> > databases with some shared data + the processes serving them). Also
> > instance:database is n:1 while cluster to database is 1:n. Very
> > different.
>
> Peter, Oracle instance manages collection of the databases and is ensuring
> recoverabilty using redo logs, which are completely analogous to WAL logs,
> if managed a bit differently. Let's not be nitpicking here. Oracle instance
> is completely analogous to Postgres cluster. If you ask me, the word cluster
> was picked to avoid the word "instance"

I'm quoting Tom Kyte here:

| In fact, it is true to say that an instance will mount and open at
| most a single database in its entire lifetime!

While that article is originally from 2009, it was last changed in 2021,
and I'd trust Tom to change something as fundamental if it wasn't true
anymore.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: DBeaver does not show all tables in a Schema

[Shaozhong SHI]

It is a new installation with all permissions.  Nothing has done to it.

Regards,

David

On Fri, 29 Oct 2021 at 14:14, negora  wrote:

> Are you sure that you're not applying a filter in the "Database
> Navigator" panel of DBeaver?
>
> Sometimes, it occurs to me that I apply a filter for certain database, I
> forget to remove it, then open another database, and some or all tables
> do not appear in the navigator.
>
>
> On 29/10/2021 12:46, Shaozhong SHI wrote:
> > I used a DBeaver to connect to postgres but it does not show all
> > tables in a schema.
> >
> > Can anyone shed light on this?
> >
> > Regards,
> >
> > David
>
>
>

Re: DBeaver does not show all tables in a Schema

[Ryan Booz]

In a recent update (not sure when), the default for DBeaver seems to have
changed so that the navigator view is set to "simple", rather than
"advanced" which shows all objects.

Right-click the server -> edit connection -> Select "General" -> verify
"Navigator View"



On Fri, Oct 29, 2021 at 9:48 AM Shaozhong SHI 
wrote:

> It is a new installation with all permissions.  Nothing has done to it.
>
> Regards,
>
> David
>
> On Fri, 29 Oct 2021 at 14:14, negora  wrote:
>
>> Are you sure that you're not applying a filter in the "Database
>> Navigator" panel of DBeaver?
>>
>> Sometimes, it occurs to me that I apply a filter for certain database, I
>> forget to remove it, then open another database, and some or all tables
>> do not appear in the navigator.
>>
>>
>> On 29/10/2021 12:46, Shaozhong SHI wrote:
>> > I used a DBeaver to connect to postgres but it does not show all
>> > tables in a schema.
>> >
>> > Can anyone shed light on this?
>> >
>> > Regards,
>> >
>> > David
>>
>>
>>

Re: Incremental backup

[Thomas Kellerer]

Peter J. Holzer schrieb am 29.10.2021 um 15:43:
>> Peter, Oracle instance manages collection of the databases and is ensuring
>> recoverabilty using redo logs, which are completely analogous to WAL logs,
>> if managed a bit differently. Let's not be nitpicking here. Oracle instance
>> is completely analogous to Postgres cluster. If you ask me, the word cluster
>> was picked to avoid the word "instance"
>
> I'm quoting Tom Kyte here:
>
> | In fact, it is true to say that an instance will mount and open at
> | most a single database in its entire lifetime!
>
> While that article is originally from 2009, it was last changed in 2021,
> and I'd trust Tom to change something as fundamental if it wasn't true
> anymore.

Well, a single "container database" could contain multiple pluggable databases.

Except for the "pluggable" part, I consider Oracle's pluggable databases quite
similar to Postgres' databases.

Postgres dblink example

[Pratik Mehta]

Hi Postgres Community,

I am an Oracle DBA with 12 years experience. The example in Postgres
dblink_connect page seems incomplete. We find it difficult to understand.
How can I get access to more examples ?

Also, in dblink page
https://www.postgresql.org/docs/12/contrib-dblink-function.html, can we get
example on how to create connname and connstr?

--
Regards,
Pratik Mehta
+91 9664022206

Re: DBeaver does not show all tables in a Schema

[Shaozhong SHI]

On Fri, 29 Oct 2021 at 14:53, Ryan Booz  wrote:

> In a recent update (not sure when), the default for DBeaver seems to have
> changed so that the navigator view is set to "simple", rather than
> "advanced" which shows all objects.
>
> Right-click the server -> edit connection -> Select "General" -> verify
> "Navigator View"
>
>
>>> I am afraid that this did not make a difference.

Regards,

David

Re: Postgres dblink example

[Tom Lane]

Pratik Mehta  writes:
> I am an Oracle DBA with 12 years experience. The example in Postgres
> dblink_connect page seems incomplete. We find it difficult to understand.
> How can I get access to more examples ?

If you're really confused, you could look at the module's regression
tests:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/dblink/sql/dblink.sql;h=7a71817d65b97fc549b4b54f531e6692f8273d58;hb=HEAD
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/dblink/expected/dblink.out;h=91cbd744a996020c44a3e98fa1c7fd224008ef98;hb=HEAD

regards, tom lane

Re: Incremental backup

[Mladen Gogala]

On 10/29/21 09:43, Peter J. Holzer wrote:

On 2021-10-29 09:03:04 -0400, Mladen Gogala wrote:

On 10/29/21 08:49, Peter J. Holzer wrote:

I don't think that's equivalent. An Oracle instance is a runtime concept
(the collection of server processes on a single machine serving a single
database (the collection of files on the disk)) whereas a PostgreSQL cluster is
both a data and a runtime concept (config + data files for several
databases with some shared data + the processes serving them). Also
instance:database is n:1 while cluster to database is 1:n. Very
different.

Peter, Oracle instance manages collection of the databases and is ensuring
recoverabilty using redo logs, which are completely analogous to WAL logs,
if managed a bit differently. Let's not be nitpicking here. Oracle instance
is completely analogous to Postgres cluster. If you ask me, the word cluster
was picked to avoid the word "instance"

I'm quoting Tom Kyte here:

| In fact, it is true to say that an instance will mount and open at
| most a single database in its entire lifetime!

While that article is originally from 2009, it was last changed in 2021,
and I'd trust Tom to change something as fundamental if it wasn't true
anymore.

 hp



Few days ago, in july 2013, Oracle has introduced version 12c with 
multi-tenant option.


http://appstech.com/2013/08/oracle-announces-general-availability-of-oracle-database-12c-the-first-database-designed-for-the-cloud/

Each Oracle instance can now manage up to 255 pluggable databases which 
are, for all intents and purposes, equivalent to Postgres databases, if 
somewhat more awkward. The "unplug" and "plug in" operations leave a lot 
of room for improvement. That makes Oracle instance functionally 
equivalent to the Postgres cluster. Current Oracle version, 21c no 
longer supports flat architecture, without pluggable databases.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Postgres dblink example

[Adrian Klaver]

On 10/29/21 07:02, Pratik Mehta wrote:

Hi Postgres Community,

I am an Oracle DBA with 12 years experience. The example in Postgres 
dblink_connect page seems incomplete. We find it difficult to 
understand. How can I get access to more examples ?


Also, in dblink page 
https://www.postgresql.org/docs/12/contrib-dblink-function.html 
, can 
we get example on how to create connname and connstr?


See:
https://www.postgresql.org/docs/current/contrib-dblink-connect.html



--
Regards,
Pratik Mehta
+91 9664022206



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: DBeaver does not show all tables in a Schema

[Adrian Klaver]

On 10/29/21 03:46, Shaozhong SHI wrote:
I used a DBeaver to connect to postgres but it does not show all tables 
in a schema.


Can anyone shed light on this?


Not without:

1) Postgres version

2) DBeaver version

3) JDBC version

4) Example of what you expect to see vs what you are seeing
In other words how do you know you are not seeing all tables?

5) Verification that you are connecting to correct database.



Regards,

David



--
Adrian Klaver
adrian.kla...@aklaver.com

psql syntax for array of strings in a variable?

[Philip Semanchuk]

Hi,
I would appreciate help with the syntax for querying an array of strings 
declared as a psql variable. Here's an example.

\set important_days ARRAY['monday', 'friday']

select 1 where 'monday' = ANY(:important_days);
ERROR:  42703: column "monday" does not exist
LINE 1: select 1 where 'monday' = ANY(ARRAY[monday,friday]);

select 1 where 'monday' = ANY(:"important_days");
ERROR:  42703: column "ARRAY[monday,friday]" does not exist
LINE 1: select 1 where 'monday' = ANY("ARRAY[monday,friday]");

I'm doing something wrong but I can't figure out what.

My real-world use case is that I have a psql script that will execute several 
queries on a long list of strings, and rather than repeat those strings over 
and over in the script, I'd like to declare them once at the top of the script 
and then refer to the variable after that. Bonus points if there's a way to do 
a multiline declaration like --

\set important_days ARRAY['monday', 
  'friday']

Thanks for reading
Philip

Re: psql syntax for array of strings in a variable?

[David G. Johnston]

On Friday, October 29, 2021, Philip Semanchuk 
wrote:

> Hi,
> I would appreciate help with the syntax for querying an array of strings
> declared as a psql variable. Here's an example.
>
> \set important_days ARRAY['monday', 'friday']


Not sure why the single quotes are getting stripped out but that is the
issue.  Maybe double them up to escape them like in a normal text literal?

Otherwise consider just doing a comma delimited string and using
string_to_array in the sql to turn it into an array at runtime.



> \set important_days ARRAY['monday',
>   'friday']
>

Not with \set.  You could write sql to output the text and then execute it
using \gexec to store the column value in a variable.  That too may be a
solution to the missing single quotes.

David J.

Re: psql syntax for array of strings in a variable?

[Pavel Stehule]

Hi

pá 29. 10. 2021 v 19:21 odesílatel Philip Semanchuk <
phi...@americanefficient.com> napsal:

> Hi,
> I would appreciate help with the syntax for querying an array of strings
> declared as a psql variable. Here's an example.
>
> \set important_days ARRAY['monday', 'friday']
>
> select 1 where 'monday' = ANY(:important_days);
> ERROR:  42703: column "monday" does not exist
> LINE 1: select 1 where 'monday' = ANY(ARRAY[monday,friday]);
>
> select 1 where 'monday' = ANY(:"important_days");
> ERROR:  42703: column "ARRAY[monday,friday]" does not exist
> LINE 1: select 1 where 'monday' = ANY("ARRAY[monday,friday]");
>
> I'm doing something wrong but I can't figure out what.
>
> My real-world use case is that I have a psql script that will execute
> several queries on a long list of strings, and rather than repeat those
> strings over and over in the script, I'd like to declare them once at the
> top of the script and then refer to the variable after that. Bonus points
> if there's a way to do a multiline declaration like --
>
> \set important_days ARRAY['monday',
>   'friday']
>
> Thanks for reading
> Philip
>

psql variables can hold only text. There is not any type - all is just text.

Regards

Pavel

Re: psql syntax for array of strings in a variable?

[Tom Lane]

"David G. Johnston"  writes:
> On Friday, October 29, 2021, Philip Semanchuk 
> wrote:
>> I would appreciate help with the syntax for querying an array of strings
>> declared as a psql variable. Here's an example.
>> 
>> \set important_days ARRAY['monday', 'friday']

> Not sure why the single quotes are getting stripped out but that is the
> issue.  Maybe double them up to escape them like in a normal text literal?

Yeah, that's just the way that \set works (and most other psql backslash
commands, I believe).  You've likely got an issue with whitespace
disappearing, too, though that might be harmless in this specific example.

regression=# \set foo 'bar baz'
regression=# \echo :foo
bar baz
regression=# \set foo 'bar ''baz'
regression=# \echo :foo
bar 'baz
regression=# \set foo bar ''baz 
regression=# \echo :foo
barbaz

Not sure offhand how well-documented this is.

regards, tom lane

Re: psql syntax for array of strings in a variable?

[Philip Semanchuk]

> On Oct 29, 2021, at 2:05 PM, Tom Lane  wrote:
> 
> "David G. Johnston"  writes:
>> On Friday, October 29, 2021, Philip Semanchuk 
>> wrote:
>>> I would appreciate help with the syntax for querying an array of strings
>>> declared as a psql variable. Here's an example.
>>> 
>>> \set important_days ARRAY['monday', 'friday']
> 
>> Not sure why the single quotes are getting stripped out but that is the
>> issue.  Maybe double them up to escape them like in a normal text literal?
> 
> Yeah, that's just the way that \set works (and most other psql backslash
> commands, I believe).  You've likely got an issue with whitespace
> disappearing, too, though that might be harmless in this specific example.
> 
> regression=# \set foo 'bar baz'
> regression=# \echo :foo
> bar baz
> regression=# \set foo 'bar ''baz'
> regression=# \echo :foo
> bar 'baz
> regression=# \set foo bar ''baz 
> regression=# \echo :foo
> barbaz
> 
> Not sure offhand how well-documented this is.

Thanks, all. Glad to know I wasn’t missing something obvious. 

> On Oct 29, 2021, at 1:52 PM, Pavel Stehule  wrote:

> psql variables can hold only text. There is not any type - all is just text.


^^^ This was especially helpful; I’d never considered that before.

Cheers
Philip