AW: [Extern] Re: postgres event trigger workaround
> > Hi, > > On Wed, Jan 12, 2022 at 11:57:45AM +, Zwettler Markus (OIZ) wrote: > > > > PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, > > CREATE TABLESPACE by definition (would be nice if they do). > > > > Is there any workaround to react with ddl_command_start behavior on > > such an event? > > That's not possible. The limitation exists because those objects are shared > objects > and therefore could be created from any database in the cluster. > > What is your use case? Maybe you could rely on logging all DDL instead for > instance. > We have the need to separate user (role) management from infrastructure (database) management. Granting CREATEROLE to any role also allows this role to create other roles having CREATEDB privileges and therefore also getting CREATEDB privileges. My use case would have been to grant CREATEROLE to any role while still restricting "create database".
Re: [Extern] Re: postgres event trigger workaround
On Fri, Jan 14, 2022 at 10:01 AM Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote: > We have the need to separate user (role) management from infrastructure > (database) management. > Granting CREATEROLE to any role also allows this role to create other > roles having CREATEDB privileges and therefore also getting CREATEDB > privileges. > My use case would have been to grant CREATEROLE to any role while still > restricting "create database". > I also which for my granular privileges around ROLEs. Lite a CREATEROLE role that can only DROP the ROLEs it created (or created by other ROLEs its a member of). Or a (NOLOGIN) ROLE that's restricted to have table privileges in some SCHEMAs only, or in SCHEMAs owned by a given ROLE only. or ROLEs local to a given DATABASE only. These kind of things. An idea I'm toying with is having a SCHEMA with (DEFINER RIGHTS) functions that acts as a wrapper around CREATE/DROP ROLE to impose custom restrictions. It would record in private tables more context at creating times, and use that context to restrict the DROPs. Could also solve your CREATEROLE vs CREATEDB conundrum maybe. I have no time to develop that idea ATM though... Nor am I sure it would work. And it would force my code to rip out it's current direct SQL DDLs, by equivalent functions from that mediator "admin" schema. It would also not solve all my issues, like some ROLEs being restricted to GRANTs from a given SCHEMA. (but maybe event trigger would allow to intercept that to check those too?) Just thinking aloud :). --DD
Re: [Extern] Re: postgres event trigger workaround
Hi, On Fri, Jan 14, 2022 at 09:01:12AM +, Zwettler Markus (OIZ) wrote: > > We have the need to separate user (role) management from infrastructure > (database) management. > > Granting CREATEROLE to any role also allows this role to create other roles > having CREATEDB privileges and therefore also getting CREATEDB privileges. > > My use case would have been to grant CREATEROLE to any role while still > restricting "create database". I see, that's indeed a problem. You could probably enforce that using some custom module to enforce additional rules on top of CREATE ROLE processing, but it would have to be written in C.
List all tables from a specific database
I work on a project that use Postgre SQL, and I have 0 experience in this regard. I hope to get help here. I need two SQL scripts for Postgre: 1. Get all databases, which I aquired already: SELECT datname FROM pg_database WHERE datistemplate = false This one is functional, it's ok. 2. Now, I need to find all tables under a specific database. This one I don't know how to achieve it. Can you help me here ? It is possible ? For instance, using this SQL script: SELECT 1, datname FROM pg_database WHERE datistemplate = false I got: mydb1mydb2postgres So far, so good. Of course, mydb1 and mydb2 have been created by me, using: create database mydb1create database mydb2 Now, which SQL script to use, to retrieve all tables under a database only, but, most important, using SQL script only. For instance, something like (pseudo-script): SELECT table_name FROM information_schema.tables WHERE database = 'mydb1'SELECT table_name FROM information_schema.tables WHERE database = 'mydb2' Kindly thank you. Flaviu.
Re: List all tables from a specific database
On 14/01/2022 10:39, Flaviu2 wrote: I work on a project that use Postgre SQL, and I have 0 experience in this regard. I hope to get help here. I need two SQL scripts for Postgre: 1. Get all databases, which I aquired already: *SELECT datname FROM pg_database WHERE datistemplate = false* This one is functional, it's ok. 2. Now, I need to find all tables *under a specific* database. This one I don't know how to achieve it. Can you help me here ? It is possible ? If it's any help, running psql with the -E switch will show you the SQL which psql generates... so then (within psql) issuing the \dt command to list tables will show you the SQL used. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: List all tables from a specific database
Le ven. 14 janv. 2022 à 12:04, Ray O'Donnell a écrit : > On 14/01/2022 10:39, Flaviu2 wrote: > > I work on a project that use Postgre SQL, and I have 0 experience in > > this regard. I hope to get help here. I need two SQL scripts for Postgre: > > > > 1. Get all databases, which I aquired already: > > > > *SELECT datname FROM pg_database WHERE datistemplate = false* > > > > This one is functional, it's ok. > > > > 2. Now, I need to find all tables *under a specific* database. This one > > I don't know how to achieve it. Can you help me here ? It is possible ? > > If it's any help, running psql with the -E switch will show you the SQL > which psql generates... so then (within psql) issuing the \dt command to > list tables will show you the SQL used. > > Ray. > > Hello, You can use this SQL command to view all the standard table (known as relations): SELECT relname FROM pg_class WHERE relkind = 'r'; And this one for partitioned tables (if you use them): SELECT relname FROM pg_class WHERE relkind = 'p'; HTH, Thomas > -- > Raymond O'Donnell // Galway // Ireland > r...@rodonnell.ie > > >
Re: List all tables from a specific database
Thanks a lot. Maybe I am not far from a solving solution. So, if I create a database, lets say (SQL script): CREATE database mydb3; How can I create a table under mydb3 ? Because, if I run: SELECT relname FROM pg_class WHERE relkind = 'r'; Got me all tables, but I don't know the database under were created. Flaviu. On Friday, January 14, 2022, 02:08:22 PM GMT+2, Thomas Boussekey wrote: Le ven. 14 janv. 2022 à 12:04, Ray O'Donnell a écrit : On 14/01/2022 10:39, Flaviu2 wrote: > I work on a project that use Postgre SQL, and I have 0 experience in > this regard. I hope to get help here. I need two SQL scripts for Postgre: > > 1. Get all databases, which I aquired already: > > *SELECT datname FROM pg_database WHERE datistemplate = false* > > This one is functional, it's ok. > > 2. Now, I need to find all tables *under a specific* database. This one > I don't know how to achieve it. Can you help me here ? It is possible ? If it's any help, running psql with the -E switch will show you the SQL which psql generates... so then (within psql) issuing the \dt command to list tables will show you the SQL used. Ray. Hello,You can use this SQL command to view all the standard table (known as relations): SELECT relname FROM pg_class WHERE relkind = 'r'; And this one for partitioned tables (if you use them): SELECT relname FROM pg_class WHERE relkind = 'p'; HTH,Thomas -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: List all tables from a specific database
Hi, On Fri, Jan 14, 2022 at 12:19:28PM +, Flaviu2 wrote: > Thanks a lot. > Maybe I am not far from a solving solution. So, if I create a database, lets > say (SQL script): > CREATE database mydb3; > How can I create a table under mydb3 ? Because, if I run: > SELECT relname FROM pg_class WHERE relkind = 'r'; > > Got me all tables, but I don't know the database under were created. On postgres all relations, functions and so on are specific to a specific database, and only accessible when connected on that specific database. So if you want to create a table in mydb3, or see the list of tables in that database, you need to connect to mydb3. If needed, you can get the current database with the current_database() function, e.g.: =# SELECT current_database(); current_database -- postgres (1 row)
Re: List all tables from a specific database
Yes, I started psql with -E, and now I see the SQL underhood. Thank you. Now I need to find how to get and how to select a specific database, I have no experience in Postgre SQL.On Friday, January 14, 2022, 01:04:47 PM GMT+2, Ray O'Donnell wrote: On 14/01/2022 10:39, Flaviu2 wrote: > I work on a project that use Postgre SQL, and I have 0 experience in > this regard. I hope to get help here. I need two SQL scripts for Postgre: > > 1. Get all databases, which I aquired already: > > *SELECT datname FROM pg_database WHERE datistemplate = false* > > This one is functional, it's ok. > > 2. Now, I need to find all tables *under a specific* database. This one > I don't know how to achieve it. Can you help me here ? It is possible ? If it's any help, running psql with the -E switch will show you the SQL which psql generates... so then (within psql) issuing the \dt command to list tables will show you the SQL used. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: List all tables from a specific database
On 14/01/2022 12:42, Flaviu2 wrote: Yes, I started psql with -E, and now I see the SQL underhood. Thank you. Now I need to find how to get and how to select a specific database, I have no experience in Postgre SQL. Well, connecting from the command line with psql, you just specify it: psql [...options...] my_database Or if you're already in psql, use the \c command: \c my_database The Postgres manual is excellent, if enormous: https://www.postgresql.org/docs/current/index.html A good place to start is the "Tutorial" section. HTH, Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: List all tables from a specific database
Yes, I ran \c mydb1 but I didn't see any SQL script underhood :( On Friday, January 14, 2022, 02:45:52 PM GMT+2, Ray O'Donnell wrote: On 14/01/2022 12:42, Flaviu2 wrote: > Yes, I started psql with -E, and now I see the SQL underhood. Thank you. > > Now I need to find how to get and how to select a specific database, I > have no experience in Postgre SQL. Well, connecting from the command line with psql, you just specify it: psql [...options...] my_database Or if you're already in psql, use the \c command: \c my_database The Postgres manual is excellent, if enormous: https://www.postgresql.org/docs/current/index.html A good place to start is the "Tutorial" section. HTH, Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: List all tables from a specific database
On 14/01/2022 12:51, Flaviu2 wrote: Yes, I ran *\c mydb1* but I didn't see any SQL script underhood :( Ah, OK. As I understand it, connecting to a database isn't something you can do in SQL: it's a function of the client, and how you do it depends on the client. - So in psql, it's a command-line argument; connecting from PHP with PDO, you use a connection string; and so on. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Upgrade 13 to 14 with replication of partitioned table
Hi,
I have tried to upgrade my cluster from version 13 to 14 using the
command: "pg_upgradecluster -m links --no-start 13 main". But upgrade
failed with messages:
-
pg_restore: creating INDEX "public.closed_sessions_closed_id_idx"
pg_restore: while processing header:
pg_restore: from header entry 3195; 1259 29484835 INDEX
closed_sessions_closed_id_idx postgres
pg_restore: error: could not execute query: ERROR: cannot use invalid
index "closed_sessions_closed_id_idx" as replica identity
While executing command:
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('29484835'::pg_catalog.oid);
CREATE UNIQUE INDEX "closed_sessions_closed_id_idx" ON ONLY
"public"."closed_sessions" USING "btree" ("closed", "id");
ALTER TABLE ONLY "public"."closed_sessions" REPLICA IDENTITY USING
INDEX "closed_sessions_closed_id_idx";
---
closed_sessions is partitioned table and column closed is used for
replica identity index.
Cluster version 13 works fine...
How can I upgrade the cluster?
Sergey Belyashov.
Re: How can a Postgres SQL script be automatically run when a new table turns up?
On Thu, Jan 13, 2022 at 4:32 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Jan 13, 2022 at 8:55 AM Shaozhong SHI > wrote: > >> >> How to create an event trigger in Postgres? When a user finished loading >> a new table on to it, the trigger can start off an script 10 minutes after >> the event? >> >> > You could have a script execute every minute (say via cron) > Vendor-specific, but on AWS, you can invoke a lambda from RDS or Aurora. 1. An event trigger function runs after CREATE TABLE and invokes a lambda. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html 2. The lambda calls StartExecution on a step function. https://docs.aws.amazon.com/step-functions/latest/apireference/API_StartExecution.html 3. In the first step of the step functions, call wait for 10 minutes. https://docs.aws.amazon.com/step-functions/latest/dg/amazon-states-language-wait-state.html 4. In the second step, have the lambda perform whatever operation(s) you need to do. No polling required, but you'd have to be running in a managed service in the Amazon Cloud. Similarly, if you are self-hosting and willing/able to write some C code or run some pl/pythonu, you could create an extension/function that performs this logic. Or again if you are self-managed and go the cron route as suggested by David Johnson, there's the extension pg_cron. – Miles Elam
Re: WAL Archiving and base backup
Greetings, * Issa Gorissen (issa-goris...@usa.net) wrote: > Thx a lot. I thought about it but was not so sure about having a complex > script (compared to the very simple version when using the exclusive backup > - but this this is deprecated...). > > I will test your option with the simpler version and post it back to it can > maybe land in PostgreSQL documentation. The PG docs show how the command works and that's it. The commands in the docs aren't intended to be actually used in production environments. Writing a full solution involves having a good understanding of the PG code and how WAL archiving, backups, et al, are done. If you're not familiar with this portion of the PG code base, I'd strongly suggest you look at using solutions written and maintained by folks who are. Trying to write documentation on how to develop a complete solution would be quite an effort and would certainly go beyond bash scripting and likely wouldn't end up getting used anyway- those who are developing such solutions are already reading through the actual code. Thanks, Stephen signature.asc Description: PGP signature
Re: WAL Archiving and base backup
On 1/14/22 12:31 PM, Stephen Frost wrote: Greetings, * Issa Gorissen (issa-goris...@usa.net) wrote: Thx a lot. I thought about it but was not so sure about having a complex script (compared to the very simple version when using the exclusive backup - but this this is deprecated...). I will test your option with the simpler version and post it back to it can maybe land in PostgreSQL documentation. The PG docs show how the command works and that's it. The commands in the docs aren't intended to be actually used in production environments. Writing a full solution involves having a good understanding of the PG code and how WAL archiving, backups, et al, are done. If you're not familiar with this portion of the PG code base, I'd strongly suggest you look at using solutions written and maintained by folks who are. Needing to read the PG source code to write a workable PITR recovery solution is a serious flaw in PG documentation (and why I use PgBackRest). The documentation of two other RDBMSs that I've worked with (Rdb/VMS and SQL Server) are perfectly clear on how to do such backups and restores with relatively small amounts of scripting. Trying to write documentation on how to develop a complete solution would be quite an effort and would certainly go beyond bash scripting and likely wouldn't end up getting used anyway- those who are developing such solutions are already reading through the actual code. Thanks, Stephen -- Angular momentum makes the world go 'round.
Re: WAL Archiving and base backup
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 1/14/22 12:31 PM, Stephen Frost wrote: > >* Issa Gorissen (issa-goris...@usa.net) wrote: > >>Thx a lot. I thought about it but was not so sure about having a complex > >>script (compared to the very simple version when using the exclusive backup > >>- but this this is deprecated...). > >> > >>I will test your option with the simpler version and post it back to it can > >>maybe land in PostgreSQL documentation. > >The PG docs show how the command works and that's it. The commands > >in the docs aren't intended to be actually used in production > >environments. Writing a full solution involves having a good > >understanding of the PG code and how WAL archiving, backups, et al, are > >done. If you're not familiar with this portion of the PG code base, I'd > >strongly suggest you look at using solutions written and maintained by > >folks who are. > > Needing to read the PG source code to write a workable PITR recovery > solution is a serious flaw in PG documentation (and why I use PgBackRest). I disagree that it's a flaw in the documentation- it's an unfortunate reality of the current core code. We shouldn't be trying to provide documentation around how to write a tool like pgbackrest, we should, instead, have a tool like pgbackrest in core with its own documentation, as most other RDBMS's do. > The documentation of two other RDBMSs that I've worked with (Rdb/VMS and SQL > Server) are perfectly clear on how to do such backups and restores with > relatively small amounts of scripting. ... using tools which are purpose built to the task, no? Thanks, Stephen signature.asc Description: PGP signature
Re: WAL Archiving and base backup
On 1/14/22 1:40 PM, Stephen Frost wrote: [snip] We shouldn't be trying to provide documentation around how to write a tool like pgbackrest, we should, instead, have a tool like pgbackrest in core with its own documentation, as most other RDBMS's do. That's an excellent solution to this problem. -- Angular momentum makes the world go 'round.
Re: WAL Archiving and base backup
On Fri, Jan 14, 2022 at 1:48 PM Ron wrote: > On 1/14/22 1:40 PM, Stephen Frost wrote: > [snip] > > We shouldn't be trying to provide > > documentation around how to write a tool like pgbackrest, we should, > > instead, have a tool like pgbackrest in core with its own documentation, > > as most other RDBMS's do. > > That's an excellent solution to this problem. > > I still don't really understand what is so great about it. About its only redeeming feature is a declaration that "it is in core" and that newcomers can just default to it without thinking. I'd rather just play favorites and write "use pgbackrest" in our documentation. Or some hybrid approach where we don't just pick one but instead guide people to the community solutions that are out there. I don't think I really want the people responsible for core to spend time on writing end-user backup tooling. Their time is much more valuably spent working on the core product. David J.
Re: WAL Archiving and base backup
>On 1/14/22 1:40 PM, Stephen Frost wrote: >snip] >> We shouldn't be trying to provide >> documentation around how to write a tool like pgbackrest, we should, >> instead, have a tool like pgbackrest in core with its own documentation, >> as most other RDBMS's do. >That's an excellent solution to this problem. While I know the reasons for not having something like pgbackrest in core, I think this is a major drawback for the project. People coming from commercial systems are used to have that tooling included by default. +1 for having that in core Regards Daniel
Re: WAL Archiving and base backup
On 1/14/22 13:04, Daniel Westermann (DWE) wrote: On 1/14/22 1:40 PM, Stephen Frost wrote: snip] We shouldn't be trying to provide documentation around how to write a tool like pgbackrest, we should, instead, have a tool like pgbackrest in core with its own documentation, as most other RDBMS's do. That's an excellent solution to this problem. While I know the reasons for not having something like pgbackrest in core, I think this is a major drawback for the project. People coming from commercial systems are used to have that tooling included by default. They are also used to paying a big whopping bill, which is why they go looking for alternatives. So like most things is it a tradeoff, sweat equity vs hard cash. +1 for having that in core Regards Daniel -- Adrian Klaver adrian.kla...@aklaver.com
Re: Assistance with an out of shared memory error
Hi Tom,
This was very helpful, thanks for the tips.
Gautam
On Thu, Jan 13, 2022 at 3:36 PM Tom Lane wrote:
> Gautam Bellary writes:
> > I've got a PL/pgSQL function (regenerate_gateway_last_seen, attached)
> that
> > loops through all partitions of 2 tables ("Measure" and
> "ValuelessMeasure",
> > schema attached) selecting data from each into another table
> > ("GatewayLastSeenNew"). Occasionally the function runs to completion, but
> > usually it errors with the message copied below. I'd appreciate any
> advice
> > to help understand why I'm seeing this and if increasing
> > max_locks_per_transaction, changing another configuration value, or
> > changing how the function works would improve reliability.
>
> Yes, you ought to raise max_locks_per_transaction ...
>
> > 2. max_locks_per_transaction is being hit in the transaction - this also
> > seems unlikely because max_locks_per_transaction is set to the default
> > value of 64, but there are ~8000 partitions to consider and I expect the
> > resulting GatewayLastSeenNew table to have thousands of rows.
>
> ... or else reduce the number of partitions you're using. (People
> frequently seem to think that more partitions are free. That is
> extremely not true. I generally think that if you're using more
> than a few dozen partitions per table, you are making a mistake.)
>
> > If I was
> > taking locks that would contribute towards that ceiling of 64 I'd expect
> > this to fail every time, instead of failing often but not always as I
> > observe.
>
> You misinterpret what that parameter does: it is not a hard per-session
> limit. This error appears when the shared lock-table pool overflows, so
> you can (usually) take a lot more than 64 locks before running into
> trouble. It matters what else is going on in the database.
>
> > 3. The max size of the lock table is being exceeded - my understanding is
> > that the lock table has room for max_locks_per_transaction *
> > (max_connections + max_prepared_transactions) locks, which would be 64 *
> > (200 + 0) = 12800 for my current configuration. I used 'SELECT COUNT(*)
> > FROM PG_LOCKS' while the function was running and observe values as high
> as
> > 21758, so if this is the issue it seems like I might not be estimating
> the
> > max size of the lock table correctly or only specific locks contribute to
> > that.
>
> I don't recall for sure, but I think that the lock table has one entry per
> lockable object, while the pg_locks view shows separate entries for
> different lock modes on the same object.
>
> regards, tom lane
>
Re: [Extern] Re: postgres event trigger workaround
Hi In my solution, all users don't need direct access to the schema because you have to use the functional API to access it. If you can manage users with functions, you can close the schema in the same way. Usually the function is executed with the permissions of the calling user, which requires permissions for all affected entities. However, if you specify the "SECURITY DEFINER" parameter at creation, the function will be executed with the owner's permissions. The owner of the function has no login permissions but has permissions on the affected entities. In this way you will close the schema from the roles that have rights to the role management functions. -- Regards, Dmitry! пт, 14 янв. 2022 г. в 15:24, Julien Rouhaud : > Hi, > > On Fri, Jan 14, 2022 at 09:01:12AM +, Zwettler Markus (OIZ) wrote: > > > > We have the need to separate user (role) management from infrastructure > (database) management. > > > > Granting CREATEROLE to any role also allows this role to create other > roles having CREATEDB privileges and therefore also getting CREATEDB > privileges. > > > > My use case would have been to grant CREATEROLE to any role while still > restricting "create database". > > I see, that's indeed a problem. You could probably enforce that using some > custom module to enforce additional rules on top of CREATE ROLE > processing, but > it would have to be written in C. > > >
Re: [Extern] Re: postgres event trigger workaround
Hi, On Sat, Jan 15, 2022 at 08:36:21AM +0500, Дмитрий Иванов wrote: > In my solution, all users don't need direct access to the schema because > you have to use the functional API to access it. If you can manage users > with functions, you can close the schema in the same way. > Usually the function is executed with the permissions of the calling user, > which requires permissions for all affected entities. However, if you > specify the "SECURITY DEFINER" parameter at creation, the function will be > executed with the owner's permissions. The owner of the function has no > login permissions but has permissions on the affected entities. In this way > you will close the schema from the roles that have rights to the role > management functions. Sure you can solve most problems with that. But you can't create a database (or a tablespace) from a function so this approach wouldn't cover all of OP's needs, as different approach would be needed for role and db creation.
Re: List all tables from a specific database
There is also INFORMATION_SCHEMA which is SQL standard and extremely easy to use. On Fri, Jan 14, 2022, 1:08 PM Thomas Boussekey wrote: > > > Le ven. 14 janv. 2022 à 12:04, Ray O'Donnell a écrit : > >> On 14/01/2022 10:39, Flaviu2 wrote: >> > I work on a project that use Postgre SQL, and I have 0 experience in >> > this regard. I hope to get help here. I need two SQL scripts for >> Postgre: >> > >> > 1. Get all databases, which I aquired already: >> > >> > *SELECT datname FROM pg_database WHERE datistemplate = false* >> > >> > This one is functional, it's ok. >> > >> > 2. Now, I need to find all tables *under a specific* database. This one >> > I don't know how to achieve it. Can you help me here ? It is possible ? >> > >> If it's any help, running psql with the -E switch will show you the SQL >> which psql generates... so then (within psql) issuing the \dt command to >> list tables will show you the SQL used. >> >> Ray. >> >> Hello, > You can use this SQL command to view all the standard table (known as > relations): > > SELECT relname FROM pg_class WHERE relkind = 'r'; > > And this one for partitioned tables (if you use them): > > SELECT relname FROM pg_class WHERE relkind = 'p'; > > HTH, > Thomas > > >> -- >> Raymond O'Donnell // Galway // Ireland >> r...@rodonnell.ie >> >> >>
