Re: the installation of pgadmin4 makes me weep in frustration

[Achilleas Mantzios]

On 14/1/19 9:58 π.μ., robert wrote:


Hi There


first, thanks a lot for the great an beautiful software we get from PostgreSQL 
and all people around it.

But I wonder how it comes, that installing pgadmin4 is so incredibly hard?

And no documentation.

I would like to install pgadmin4 to my ubuntu 18.1 laptop.

Where do I find doku on how to install pgadmin4. Preferably I would like to 
install it using pip?


it's no longer a standalone app, but rather a web app, follow the README's in 
the source. At least that was what I did, and I agree that it was not straight 
forward.



thanks

robert

--
Robert Rottermann CEO

031 333 10 20
rob...@redo2oo.ch 
Sickingerstrasse 3, 3014 Bern 

https://Redo2oo.ch 

*Ihr Partner wenn es um ERP Lösungen geht.*




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

repmgr and SSH

[ROS Didier]

Hi
   I would like to setup a repmgr configuration with one primary 
node, one standby node and one witness node.
   Regarding SSH configuration, the documentation is not clear, I 
think.
Do we need to setup SSH between the three nodes or only between primary and 
standby nodes ?

   Thanks in advance

Best Regards
[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD







Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à 
l'intention exclusive des destinataires et les informations qui y figurent sont 
strictement confidentielles. Toute utilisation de ce Message non conforme à sa 
destination, toute diffusion ou toute publication totale ou partielle, est 
interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le 
copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si 
vous avez reçu ce Message par erreur, merci de le supprimer de votre système, 
ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support 
que ce soit. Nous vous remercions également d'en avertir immédiatement 
l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de toute 
erreur ou virus.


This message and any attachments (the 'Message') are intended solely for the 
addressees. The information contained in this Message is confidential. Any use 
of information contained in this Message not in accord with its purpose, any 
dissemination or disclosure, either whole or partial, is prohibited except 
formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any 
part of it. If you have received this message in error, please delete it and 
all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or 
virus-free.

Re: the installation of pgadmin4 makes me weep in frustration

[Steve Atkins]

> On Jan 14, 2019, at 7:58 AM, robert  wrote:
> 
> Hi There
> 
> 
> 
> first, thanks a lot for the great an beautiful software we get from 
> PostgreSQL and all people around it.
> 
> But I wonder how it comes, that installing pgadmin4 is so incredibly hard?
> 
> And no documentation.
> 
> I would like to install pgadmin4 to my ubuntu 18.1 laptop.
> 
> Where do I find doku on how to install pgadmin4. Preferably I would like to 
> install it using pip?

pgadmin 4 is nothing to do with the PostgreSQL project itself, it's just a 
third party client.

There are many other third-party clients listed here - 
https://wiki.postgresql.org/wiki/PostgreSQL_Clients -
most of them probably better than pgadmin4.

Cheers,
  Steve

Re: the installation of pgadmin4 makes me weep in frustration

[Tony Shelver]

Just over a year ago, I started to look for free / open source clients for
Postgres, and went through most of that list.

Unless you are willing to pay for a commercial license, most of them are
not close to PG or have some limitations that made them unusable for me.
Several 'free' tools were limited as to the number of tables, databases or
whatever that they could handle.

Currently I am on PGAdmin 4.4, and it works fine for me (on Windows 10),
and has kept up with all the functionality available in Postgres, such as
procedures.

On Mon, 14 Jan 2019 at 12:25, Steve Atkins  wrote:

>
>
> > On Jan 14, 2019, at 7:58 AM, robert  wrote:
> >
> > Hi There
> >
> >
> >
> > first, thanks a lot for the great an beautiful software we get from
> PostgreSQL and all people around it.
> >
> > But I wonder how it comes, that installing pgadmin4 is so incredibly
> hard?
> >
> > And no documentation.
> >
> > I would like to install pgadmin4 to my ubuntu 18.1 laptop.
> >
> > Where do I find doku on how to install pgadmin4. Preferably I would like
> to install it using pip?
>
> pgadmin 4 is nothing to do with the PostgreSQL project itself, it's just a
> third party client.
>
> There are many other third-party clients listed here -
> https://wiki.postgresql.org/wiki/PostgreSQL_Clients -
> most of them probably better than pgadmin4.
>
> Cheers,
>   Steve
>

Re: the installation of pgadmin4 makes me weep in frustration

[William Ivanski]

Hi Robert,

Please try OmniDB: https://omnidb.org/en/

OmniDB is a database management tool that can be installed on Linux,
Windows and
MacOS, offering several features for managing PostgreSQL databases, for
example:

- monitoring dashboard
- query plan visualization
- psql-like console tab
- debugger for PL/pgSQL functions
- support for pglogical, Postgres-BDR and Postgres-XL as OmniDB plugins

OmniDB also offers basic management for Oracle, MariaDB and MySQL databases.
OmniDB is open source software and sponsored by 2ndQuadrant.

Hi Tony, OmniDB has evolved a lot since a year ago. Please checkout new
features
and improvements. A new release comes out every two months.

The developers try to include all the requests done via GitHub issues, so
if you
think something is missing, please just request there.

Best regards,

William

On Mon, Jan 14, 2019 at 9:01 AM Tony Shelver  wrote:

> Just over a year ago, I started to look for free / open source clients for
> Postgres, and went through most of that list.
>
> Unless you are willing to pay for a commercial license, most of them are
> not close to PG or have some limitations that made them unusable for me.
> Several 'free' tools were limited as to the number of tables, databases or
> whatever that they could handle.
>
> Currently I am on PGAdmin 4.4, and it works fine for me (on Windows 10),
> and has kept up with all the functionality available in Postgres, such as
> procedures.
>
> On Mon, 14 Jan 2019 at 12:25, Steve Atkins  wrote:
>
>>
>>
>> > On Jan 14, 2019, at 7:58 AM, robert  wrote:
>> >
>> > Hi There
>> >
>> >
>> >
>> > first, thanks a lot for the great an beautiful software we get from
>> PostgreSQL and all people around it.
>> >
>> > But I wonder how it comes, that installing pgadmin4 is so incredibly
>> hard?
>> >
>> > And no documentation.
>> >
>> > I would like to install pgadmin4 to my ubuntu 18.1 laptop.
>> >
>> > Where do I find doku on how to install pgadmin4. Preferably I would
>> like to install it using pip?
>>
>> pgadmin 4 is nothing to do with the PostgreSQL project itself, it's just
>> a third party client.
>>
>> There are many other third-party clients listed here -
>> https://wiki.postgresql.org/wiki/PostgreSQL_Clients -
>> most of them probably better than pgadmin4.
>>
>> Cheers,
>>   Steve
>>
>

-- 
William Ivanski - 2ndQuadrant
PostgreSQL Development, 24x7 Support & Remote DBA, Training & Services

Re: the installation of pgadmin4 makes me weep in frustration

[Murtuza Zabuawala]

Thanks to Postgres Debian/Ubuntu packaging team, Installing pgAdmin4 is
straightforward task,

1) Create the file /etc/apt/sources.list.d/pgdg.list and add a line for the
repository
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

2) Import the repository signing key, and update the package lists
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo
apt-key add -

3) sudo apt-get update

4) sudo apt-get install pgadmin4

Check https://www.postgresql.org/download/linux/ubuntu/ for more
information.

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



On Mon, Jan 14, 2019 at 1:29 PM robert  wrote:

> Hi There
>
>
> first, thanks a lot for the great an beautiful software we get from
> PostgreSQL and all people around it.
>
> But I wonder how it comes, that installing pgadmin4 is so incredibly hard?
>
> And no documentation.
>
> I would like to install pgadmin4 to my ubuntu 18.1 laptop.
>
> Where do I find doku on how to install pgadmin4. Preferably I would like
> to install it using pip?
>
>
> thanks
>
> robert
> --
> Robert Rottermann CEO
>
> 031 333 10 20
> rob...@redo2oo.ch
> Sickingerstrasse 3, 3014 Bern
> 
>
> [image: https://Redo2oo.ch] 
> *Ihr Partner wenn es um ERP Lösungen geht.*
>

Re: the installation of pgadmin4 makes me weep in frustration

[Ravi Krishna]

> 
> pgadmin 4 is nothing to do with the PostgreSQL project itself, it's just a 
> third party client.
> 
> There are many other third-party clients listed here - 
> https://wiki.postgresql.org/wiki/PostgreSQL_Clients -
> most of them probably better than pgadmin4.

Agreed.  I use dbeaver and it is very good.

sha512sum (program) gives different result than sha512 in PG11

[Andreas Joseph Krogh]

Hi.
 
Anyone can explain why these two don't give the same result?
 
1.
$ echo "A" | sha512sum 
 
7a296fab5364b34ce3e0476d55bf291bd41aa085e5ecf2a96883e593aa1836fed22f7242af48d54af18f55c8d1def13ec9314c92a0ba63f7663500090565
 
 -
  
2.
$ psql -A -t -c "select encode(sha512('A'), 'hex')" 
 
21b4f4bd9e64ed355c3eb676a28ebedaf6d8f17bdc365995b319097153044080516bd083bfcce66121a3072646994c8430cc382b8dc543e84880183bf856cff5
  
Thanks!
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: sha512sum (program) gives different result than sha512 in PG11

[Thomas Markus]

Hi,

echo contains a trailing carriage return. Try

echo -n "A" | sha512sum

regards
Thomas


Am 14.01.19 um 16:16 schrieb Andreas Joseph Krogh:

Hi.
Anyone can explain why these two don't give the same result?
1.
$ echo "A" | sha512sum
7a296fab5364b34ce3e0476d55bf291bd41aa085e5ecf2a96883e593aa1836fed22f7242af48d54af18f55c8d1def13ec9314c92a0ba63f7663500090565 
 -

2.
$ psql -A -t -c "select encode(sha512('A'), 'hex')"
21b4f4bd9e64ed355c3eb676a28ebedaf6d8f17bdc365995b319097153044080516bd083bfcce66121a3072646994c8430cc382b8dc543e84880183bf856cff5
Thanks!
--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 

Sv: Re: sha512sum (program) gives different result than sha512 in PG11

[Andreas Joseph Krogh]

På mandag 14. januar 2019 kl. 16:18:30, skrev Thomas Markus <
t.mar...@proventis.net >:
Hi,

 echo contains a trailing carriage return. Try

 echo -n "A" | sha512sum

 regards
 Thomas  
Ha ha, didn't think of that!
 
Thanks:-)
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 
 

pg_restore restores privileges differently from psql

[Sherrylyn Branchaw]

Hi,

I'm running two Postgres 9.6.11 databases on RHEL 6.9.

I'm restoring the schema from one database (prod) to another (dev). There
are users that exist in prod that don't exist in dev. When the restore job
tries to grant privileges to nonexistent users in dev, I would like it to
generate an error, which is safe to ignore, but still correctly grant
privileges to any user that does exist in dev.

That's the behavior I see when I dump to a plain file and restore it
using *psql
-f*, but not the behavior I see when I do a *pg_dump -Fc* followed by
*pg_restore.* *pg_restore *seems to treat all the *GRANT* statements for a
single object as a single statement, and when one errors out, they all
error out, meaning I'm left with no privileges on the object in question.

For instance, when this appears in my plaintext file:
GRANT ALL ON SCHEMA test TO user1;
GRANT USAGE ON SCHEMA test TO user2;
GRANT USAGE ON SCHEMA test TO user3;

and user1 doesn't exist on the target database, user2 and user3 get the
expected privileges when restoring from *psql* but not from *pg_restore*.

Here's a reproducible test case.

CREATE DATABASE prod_db;
CREATE DATABASE dev_db_pg_restore;
CREATE DATABASE dev_db_psql;
CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;

-- in prod_db
CREATE SCHEMA test;
GRANT ALL ON SCHEMA test TO prod_user;
GRANT USAGE ON SCHEMA test TO prod_and_dev_user;

pg_dump -Fc prod_db &> prod_dump.bin
pg_dump prod_db &> prod_dump.sql

-- On database instance containing the dev dbs.
DROP ROLE prod_user;

pg_restore prod_dump.bin -d dev_db_pg_restore
psql -d dev_db_psql -f prod_dump.sql

-- In dev_db_psql
SELECT has_schema_privilege('prod_and_dev_user', 'test', 'usage');
Expected result: true
Actual result: true

-- In dev_db_pg_restore
SELECT has_schema_privilege('prod_and_dev_user', 'test', 'usage');
Expected result: true
Actual result: false

The behavior seems to be related to the fact that *pg_restore* reports the
failed command as containing all the semicolon-delimited privilege command,
which get executed separately when restoring from plaintext:

pg_restore: [archiver (db)] could not execute query: ERROR:  role
"prod_user" does not exist
Command was: GRANT ALL ON SCHEMA test TO prod_user;
GRANT USAGE ON SCHEMA test TO prod_and_dev_user;

As a workaround, I've created a unprivileged dummy user by this name on the
dev database, but my question is, is this a bug or feature? If a feature,
is the behavior documented? I didn't find any documentation, but that
doesn't mean it doesn't exist.

Thanks,
Sherrylyn

Re: Question about MemoryContextRegisterResetCallback

[Michel Pelletier]

After absorbing some of the code you've pointed out I have a couple of
questions about my understanding before I start hacking on making expanded
matrices.

Serializing sparse matrices can be done with _expand/_build functions, and
the size is known, so I can implement the EOM_flatten_into_methods.  From
the array examples, it looks like accessor functions are responsible for
detecting and unflattening themselves, so I think I've got that understood.

Reading expandeddatum.h says "The format appearing on disk is called the
data type's "flattened" representation. since it is required to be a
contiguous blob of bytes -- but the type can have an expanded
representation that is not.  Data types must provide means to translate an
expanded representation back to flattened form."

It mentions "on disk" does this mean the flattened representation must be
binary compatible with what matrix_send emits?  They will likely be the
same now, so I can see this as a convenience, but is it a requirement?
Future matrix_send implementations may do some form of compressed sparse
row format, which would be inefficient for in-memory copies.

Thanks again,

-Michel

On Sun, Jan 13, 2019 at 10:51 AM Michel Pelletier <
pelletier.mic...@gmail.com> wrote:

> On Sun, Jan 13, 2019 at 9:30 AM Tom Lane  wrote:
>
>> I suppose what you're doing is returning a pointer to a GraphBLAS object
>> as a Datum (or part of a pass-by-ref Datum)?  If so, that's not going
>> to work terribly well, because it ignores the problem that datatype-
>> independent code is going to assume it can copy Datum values using
>> datumCopy() or equivalent logic.  More often than not, such copying
>> is done to move the value into a different memory context in preparation
>> for freeing the original context.  If you delete the GraphBLAS object
>> when the original context is deleted, you now have a dangling pointer
>> in the copy.
>>
>> We did invent some infrastructure awhile ago that could potentially
>> handle this sort of situation: it's the "expanded datum" stuff.
>> The idea here would be that your representation involving a GraphBLAS
>> pointer would be an efficient-to-operate-on expanded object.  You
>> would need to be able to serialize and deserialize that representation
>> into plain self-contained Datums (probably varlena blobs), but hopefully
>> GraphBLAS is capable of going along with that.  You'd still need a
>> memory context reset callback attached to each expanded object, to
>> free the associated GraphBLAS object --- but expanded objects are
>> explicitly aware of which context they're in, so at least in principle
>> that should work.  (I'm not sure anyone's actually tried to build
>> an expanded-object representation that has external resources, so
>> we might find there are some bugs to fix there.)
>>
>> Take a look at
>>
>> src/include/utils/expandeddatum.h
>> src/backend/utils/adt/expandeddatum.c
>> src/backend/utils/adt/array_expanded.c
>> src/backend/utils/adt/expandedrecord.c
>>
>>
> Ah I see, the water is much deeper here.  Thanks for the detailed
> explanation, expandeddatum.h was very helpful and I see now how
> array_expanded works.  If I run into any problems registering my callback
> in the expanded context I'll repost back.
>
> Thanks Tom!
>
> -Michel
>
>
>> regards, tom lane
>>
>

Re: pg_restore restores privileges differently from psql

[Adrian Klaver]

On 1/14/19 10:15 AM, Sherrylyn Branchaw wrote:

Hi,

I'm running two Postgres 9.6.11 databases on RHEL 6.9.

I'm restoring the schema from one database (prod) to another (dev). 
There are users that exist in prod that don't exist in dev. When the 
restore job tries to grant privileges to nonexistent users in dev, I 
would like it to generate an error, which is safe to ignore, but still 
correctly grant privileges to any user that does exist in dev.


That's the behavior I see when I dump to a plain file and restore it 
using /psql -f/, but not the behavior I see when I do a /pg_dump 
-Fc/ followed by /pg_restore./ /pg_restore /seems to treat all the 
/GRANT/ statements for a single object as a single statement, and when 
one errors out, they all error out, meaning I'm left with no privileges 
on the object in question.


For instance, when this appears in my plaintext file:
GRANT ALL ON SCHEMA test TO user1;
GRANT USAGE ON SCHEMA test TO user2;
GRANT USAGE ON SCHEMA test TO user3;

and user1 doesn't exist on the target database, user2 and user3 get the 
expected privileges when restoring from /psql/ but not from /pg_restore/.


Here's a reproducible test case.

CREATE DATABASE prod_db;
CREATE DATABASE dev_db_pg_restore;
CREATE DATABASE dev_db_psql;
CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;

-- in prod_db
CREATE SCHEMA test;
GRANT ALL ON SCHEMA test TO prod_user;
GRANT USAGE ON SCHEMA test TO prod_and_dev_user;

pg_dump -Fc prod_db &> prod_dump.bin
pg_dump prod_db &> prod_dump.sql

-- On database instance containing the dev dbs.
DROP ROLE prod_user;


The above needs more information:

1) Are the dev_* databases on a different cluster?

2) If so did you run:

CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;

on that cluster first?

Also if so:

In the restores below are you sure you are pointed at the same cluster 
in each case?




pg_restore prod_dump.bin -d dev_db_pg_restore
psql -d dev_db_psql -f prod_dump.sqlu 


What do you see if you do:

pg_restore -f prod_dump_restore.sql prod_dump.bin

and look in prod_dump_res?tore.sql?



-- In dev_db_psql
SELECT has_schema_privilege('prod_and_dev_user', 'test', 'usage');
Expected result: true
Actual result: true

-- In dev_db_pg_restore
SELECT has_schema_privilege('prod_and_dev_user', 'test', 'usage');
Expected result: true
Actual result: false

The behavior seems to be related to the fact that /pg_restore/ reports 
the failed command as containing all the semicolon-delimited privilege 
command, which get executed separately when restoring from plaintext:


pg_restore: [archiver (db)] could not execute query: ERROR:  role 
"prod_user" does not exist

     Command was: GRANT ALL ON SCHEMA test TO prod_user;
GRANT USAGE ON SCHEMA test TO prod_and_dev_user;

As a workaround, I've created a unprivileged dummy user by this name on 
the dev database, but my question is, is this a bug or feature? If a 
feature, is the behavior documented? I didn't find any documentation, 
but that doesn't mean it doesn't exist.


Thanks,
Sherrylyn



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

Re: pg_restore restores privileges differently from psql

[Sherrylyn Branchaw]

The above needs more information:

1) Are the dev_* databases on a different cluster?

2) If so did you run:

CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;

on that cluster first?

I happened to put them all on the same cluster for my test case, in order
to reproduce the unexpected behavior I encountered in the wild, where the
prod and dev dbs happened to live on different clusters. In short, as long
as you make sure the *prod_user* exists on the source cluster at the time
when the dump is taken, and doesn't exist on the target cluster when the
restore is carried out, you get the behavior I saw.

Also if so:

In the restores below are you sure you are pointed at the same cluster
in each case?

Yes, I am sure. Both for the test case I was creating for the mailing list,
and for the script where I first encountered this in the wild. Worked like
a charm when I used *psql*, didn't do what I expected when I used
*pg_restore*.

What do you see if you do:

pg_restore -f prod_dump_restore.sql prod_dump.bin

and look in prod_dump_res?tore.sql?

This is exactly what I did when I was first trying to figure out what was
going on. I see

GRANT USAGE ON SCHEMA test TO prod_and_dev_user;
GRANT ALL ON SCHEMA test TO prod_user;

If I then use *psql* to load *prod_dump_restore.sql* to a cluster that
doesn't have the *prod_user *role, I get the expected behavior (
*prod_and_dev_user* has usage on the schema *test*), because *psql* treats
each of those statements as a separate command. *pg_restore* seems to treat
them as a single command, judging by the error message and the behavior.

Best,
Sherrylyn

Re: pg_restore restores privileges differently from psql

[Adrian Klaver]

On 1/14/19 12:04 PM, Sherrylyn Branchaw wrote:

The above needs more information:

1) Are the dev_* databases on a different cluster?

2) If so did you run:

CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;

on that cluster first?

I happened to put them all on the same cluster for my test case, in 
order to reproduce the unexpected behavior I encountered in the wild, 
where the prod and dev dbs happened to live on different clusters. In 


I don't see how that can work:

test=# \c prod_db
You are now connected to database "prod_db" as user "postgres".
prod_db=# CREATE SCHEMA test;
CREATE SCHEMA
prod_db=# GRANT ALL ON SCHEMA test TO prod_user; 



GRANT 



prod_db=# GRANT USAGE ON SCHEMA test TO prod_and_dev_user; 



GRANT

prod_db=# \c dev_db_psql
You are now connected to database "dev_db_psql" as user "postgres". 



dev_db_psql=# DROP ROLE prod_user;
ERROR:  role "prod_user" cannot be dropped because some objects depend 
on it 


DETAIL:  1 object in database prod_db


short, as long as you make sure the /prod_user/ exists on


 the source
cluster at the time when the dump is taken, and doesn't exist on the 
target cluster when the restore is carried out, you get the behavior I saw.


Also if so:

In the restores below are you sure you are pointed at the same cluster
in each case?

Yes, I am sure. Both for the test case I was creating for the mailing 
list, and for the script where I first encountered this in the wild. 
Worked like a charm when I used /psql/, didn't do what I expected when I 
used /pg_restore/.


What do you see if you do:

pg_restore -f prod_dump_restore.sql prod_dump.bin

and look in prod_dump_res?tore.sql?

This is exactly what I did when I was first trying to figure out what 
was going on. I see


GRANT USAGE ON SCHEMA test TO prod_and_dev_user;
GRANT ALL ON SCHEMA test TO prod_user;

If I then use /psql/ to load /prod_dump_restore.sql/ to a cluster that 
doesn't have the /prod_user /role, I get the expected behavior 
(/prod_and_dev_user/ has usage on the schema /test/), because /psql/ 
treats each of those statements as a separate command. /pg_restore/ 
seems to treat them as a single command, judging by the error message 
and the behavior.


Best,
Sherrylyn



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

Re: pg_restore restores privileges differently from psql

[Sherrylyn Branchaw]

I don't see how that can work:

test=# \c prod_db
You are now connected to database "prod_db" as user "postgres".
prod_db=# CREATE SCHEMA test;
CREATE SCHEMA
prod_db=# GRANT ALL ON SCHEMA test TO prod_user;


GRANT


prod_db=# GRANT USAGE ON SCHEMA test TO prod_and_dev_user;


GRANT

prod_db=# \c dev_db_psql
You are now connected to database "dev_db_psql" as user "postgres".


dev_db_psql=# DROP ROLE prod_user;
ERROR:  role "prod_user" cannot be dropped because some objects depend
on it

DETAIL:  1 object in database prod_db
Yes, if you're going to put all your databases on the same cluster, you
first have to remove dependent objects before dropping the role. There are
multiple ways of going about that: dropping the database, revoking the
privileges on the objects in question, etc. If you put the databases on
different clusters and make sure you create only the prod_and_dev_user on
the second cluster, you won't run into this issue.

The goal is to make sure the prod_user role exists when the dump is taken
and doesn't exist when the restore is done. You can do this by putting the
databases on separate clusters and creating the appropriate roles, or by
dropping the user on the single cluster.

Sorry, I considered spelling all this out in the original post, because
there are two different ways of going about making sure the user isn't
present for the restore, but it seemed unnecessarily complicated, and I
thought I would let people decide what makes sense in their own environment
for testing. Sorry if that led to more confusion in the end. Just make sure
the user exists when you need it to exist and doesn't exist when you need
it not to exist, and test both the pg_restore and psql methods, and I
expect you'll see the same behavior I did (and if not, I'll be very
curious).

Best,
Sherrylyn

Re: Question about MemoryContextRegisterResetCallback

[Tom Lane]

Michel Pelletier  writes:
> It mentions "on disk" does this mean the flattened representation must be
> binary compatible with what matrix_send emits?  They will likely be the
> same now, so I can see this as a convenience, but is it a requirement?

No, your on-the-wire representation for send/recv can be different from
what you put on-disk.  In fact, typically I'd recommend that it *should*
be different to some extent, to insulate COPY BINARY data from internal
representation choices and simplify any client code that might look at
the "binary" format.  See for example numeric_send/recv, which don't
just transmit the internal format as-is --- and that was a good thing
because it let us implement various storage optimizations over the years
without breaking COPY BINARY compatibility.  It's also a good idea to
try to make the on-the-wire representation independent of server
endianness and alignment rules.

The point of the comment you're looking at is that the "flat" varlena
representation that you have to translate to/from is the same as what
will be on-disk if the datum gets stored someplace.

regards, tom lane

Re: pg_restore restores privileges differently from psql

[Tom Lane]

Sherrylyn Branchaw  writes:
> I'm restoring the schema from one database (prod) to another (dev). There
> are users that exist in prod that don't exist in dev. When the restore job
> tries to grant privileges to nonexistent users in dev, I would like it to
> generate an error, which is safe to ignore, but still correctly grant
> privileges to any user that does exist in dev.

> That's the behavior I see when I dump to a plain file and restore it
> using *psql
> -f*, but not the behavior I see when I do a *pg_dump -Fc* followed by
> *pg_restore.* *pg_restore *seems to treat all the *GRANT* statements for a
> single object as a single statement, and when one errors out, they all
> error out, meaning I'm left with no privileges on the object in question.

Yeah, this is a known issue --- the various GRANTs for a specific object
are stored in a single "TOC entry" in the archive, which pg_restore will
send to the server in a single PQexec call, causing them to be effectively
one transaction.  The easiest way to deal with it is to not send
pg_restore's output directly to the target server, but feed it through
psql, something like

pg_restore  ... | psql [connection parameters]

There's been some discussion of a real fix, but it seems messy.
pg_restore doesn't have a parser that would be adequate to separate
out multiple SQL commands in a TOC entry, and we'd rather not try
to give it one (mainly because of fear of cross-version compatibility
issues).

regards, tom lane

Re: pg_restore restores privileges differently from psql

[Adrian Klaver]

On 1/14/19 12:57 PM, Sherrylyn Branchaw wrote:

I don't see how that can work:

test=# \c prod_db
You are now connected to database "prod_db" as user "postgres".
prod_db=# CREATE SCHEMA test;
CREATE SCHEMA
prod_db=# GRANT ALL ON SCHEMA test TO prod_user;


GRANT


prod_db=# GRANT USAGE ON SCHEMA test TO prod_and_dev_user;


GRANT

prod_db=# \c dev_db_psql
You are now connected to database "dev_db_psql" as user "postgres".


dev_db_psql=# DROP ROLE prod_user;
ERROR:  role "prod_user" cannot be dropped because some objects depend
on it

DETAIL:  1 object in database prod_db
Yes, if you're going to put all your databases on the same cluster, you 
first have to remove dependent objects before dropping the role. There 
are multiple ways of going about that: dropping the database, revoking 
the privileges on the objects in question, etc. If you put the databases 
on different clusters and make sure you create only the 
prod_and_dev_user on the second cluster, you won't run into this issue.


The goal is to make sure the prod_user role exists when the dump is 
taken and doesn't exist when the restore is done. You can do this by 
putting the databases on separate clusters and creating the appropriate 
roles, or by dropping the user on the single cluster.


Sorry, I considered spelling all this out in the original post, because 
there are two different ways of going about making sure the user isn't 


True. The thing is that there are a lot of moving parts to this and the 
more detail you give the better the chance that someone trying to 
replicate actually does replicate your setup. Removes some of the 
uncertainty when the results diverge.


present for the restore, but it seemed unnecessarily complicated, and I 
thought I would let people decide what makes sense in their own 
environment for testing. Sorry if that led to more confusion in the end. 
Just make sure the user exists when you need it to exist and doesn't 
exist when you need it not to exist, and test both the pg_restore and 
psql methods, and I expect you'll see the same behavior I did (and if 
not, I'll be very curious).'


Well if I use two clusters and:

DROP ROLE prod_user;

on the one that has the dev_* databases then I can replicate.

I am interested in this as I have noted another divergence between a 
text dump and a binary dump, which as yet remains unanswered:


https://www.postgresql.org/message-id/b7a24043-1c9c-1876-f06a-8f916293c142%40aklaver.com




Best,
Sherrylyn



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

Re: (Again) Column Store on PostGreSQL

[Simon AUBERT]

Thanks Pavel and Pascal.

I guess I was really optimistic about an early implementation ^^


Best regards,

Simon

Le ven. 4 janv. 2019 à 18:35, Pavel Stehule  a
écrit :

> Hi
>
> pá 4. 1. 2019 v 17:12 odesílatel Simon AUBERT 
> napsal:
>
>> Hello,
>>
>> We can find this very informative blog post :
>> https://blog.2ndquadrant.com/column-store-plans/
>>
>> And this wiki page :
>> https://wiki.postgresql.org/wiki/ColumnOrientedSTorage
>> I must say the approach with the "orientation" option is a genius idea.
>>
>> I won't discuss much the advantages of COS, I had three years using
>> Vertica -I loved it-, some tests of Monetdb, and recently Column Store
>> Index on MSQL Server etc. And I'm pretty sure everybody is convinced this
>> works great for Instant Analytics with products such as Tableau or
>> Spotfire.. and much better than, saying, Hive. MonetDb is not so much
>> enterprise ready (not even paid support available), CH is young while PGSQL
>> has proven its high value in Transactionnal DB, the only thing missing for
>> even a bigger deployment is this feature.
>>
>> From what I understand, there is a team at 2ndquadrant.com that works on
>> it (but I'm not sure it's still in the dev pipe).
>>
>> My questions :
>> -do you develop from scratch or do you plan to use some code of
>> MonetDb/Clickhouse/C_Store_fdw for that?  I don't know if even a
>> collaboration between teams is feasible (I may be very naive but that would
>> kick ass ^^)
>>
>
>  With high probability the code should be written from scratch - every
> database has lot of unique features, code base, memory management,
> optimizer, .. It is hard, almost impossible to reuse some code from other
> database.
>
> Regards
>
> Pavel
>
> .
>> -is it still in the pipe? do you have an idea of the workload or even ETA?
>>
>> -I'm not a developer myself but I would be interested in testing,
>> benchmarking, etc.. how to get involved?
>>
>> Best regards,
>>
>> --
>> Simon AUBERT
>> aubert.si...@gmail.com
>> +33 (0)6 66 28 52 04
>>
>

-- 
Simon AUBERT
aubert.si...@gmail.com
+33 (0)6 66 28 52 04