Re: Real application clustering in postgres.
On Thu, 2020-03-05 at 17:06 +, Virendra Kumar wrote: > Failover is easy but failback is little bit tricky. > I have implemented failback by doing following steps: > > 1. Start original primary which will be doing crash recovery. It should be > designed in such a way that once it is up application should not start > connecting to it otherwise there will be split brain > and data-mistach between two instances. I implemented it by using a virtual > IP mounting on server which is actual primary using keepalived. > 2. Shutdown original primary and do a pg_rewind to make that as slave for new > primary. > 3. Once slave (original primary) is caught up with primary do failback > 4. Repeat steps #1-#3 to make failed over instance slave again. Or you use Patroni and simply say patronictl.py switchover --candidate db2 --scheduled '2020-03-06 12:00:00' mycluster Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Real application clustering in postgres.
On 3/5/20 6:07 AM, Laurenz Albe wrote: On Thu, 2020-03-05 at 07:45 +, Daulat Ram wrote: Is there any possibility/options to setup a real application clustering in Postgres as in Oracle we have a RAC feature. No, and as far as I know nobody feels interested in providing it. RAC is a complicated architecture that doesn't do much good, so most people feel that it would be a waste of time and effort. RAC ist not really a scaling solution: because of the shared storage, you can only scale for more CPUs; I/O remains the bottleneck. RAC is not really a high availability solution: because of the shared storage, it has a sibgle point of failure. This is utter nonsense. Dual redundant storage controllers connected to disks in RAID-10 configurations have been around for *at least* 25 years. Oracle got it's clustering technology from DEC, and I *know* that works. Cluster members, storage controllers and disks have all gone down, while the database and application keep on humming along. -- Angular momentum makes the world go 'round.
How to allow users to create and modify tables only in their own schemas, but with generic table owner
Hi List I'd like to setup my database in a way that only a superuser may create schemas, then grants permission to a specific user to create tables inside this schema. This should work so far with GRANT CREATE ON SCHEMA ... TO user_a. However I want the table owner not to be the user that creates the tables. Instead the owner should rather be a generic role (e.g. table_owner), and the owner should be the same over all tables of the whole database. This would work, too, if I grant membership in role table_owner to all users that may create tables. (The users must issue a SET ROLE table_owner before creating tables.) What I didn't achieve so far is making sure that user_a who created tables in schema_a cannot crete/modify tables of schema_b that were created by user_b. Do you see any way to achieve this, while still sticking to that generic owner role? Thanks a lot for your thoughts. Andy
geting results of query in plperl
I have looked at:
https://www.postgresql.org/docs/8.4/plperl-database.html
I am also comfortable querying data from tables in perl. But I do not
quite see how to get the results of a query in plperl. Here is what I
tried, and it is not working:
my $rv2 = spi_exec_query('SELECT current_user');
my $user = $rv2->{rows}[1]->{my_column};
I have used this query in SQL functions, so I know it works. I also ran it
in plsql.
What do I have wrong here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Limit transaction lifetime
Is there a way to limit a transaction lifetime in PostgreSQL? I could use `idle_in_transaction_session_timeout` parameter but it applies only to IDLE transactions. However, I want to rollback any transaction that executes more than specified period of time. -- With best regards, Andrei Zhidenkov.
Re: Real application clustering in postgres.
On Fri, 2020-03-06 at 03:25 -0600, Ron wrote: > > RAC is not really a high availability solution: because of the shared > > storage, it has a sibgle point of failure. > > This is utter nonsense. Dual redundant storage controllers > connected to disks in RAID-10 configurations have been around for at > least 25 years. > > Oracle got it's clustering technology from DEC, and I know > that works. Cluster members, storage controllers and disks have all > gone down, while the database and application keep on humming along. I am not saying that it is buggy, it is limited by design. If you have mirrored disks, and you write junk (e.g, because of a flaw in a fibre channel cable, something I have witnessed), then you have two perfectly fine copies of the junk. I am not saying the (physical) disk is the single point of failure, the (logical) file system is (Oracle calls it ASM / tablespace, but it is still a file system). Yours, Laurenz Albe
Re: How to allow users to create and modify tables only in their own schemas, but with generic table owner
On Fri, 2020-03-06 at 11:04 +, Schmid Andreas wrote: > I'd like to setup my database in a way that only a superuser may create > schemas, > then grants permission to a specific user to create tables inside this schema. > This should work so far with GRANT CREATE ON SCHEMA ... TO user_a. > However I want the table owner not to be the user that creates the tables. > Instead the owner should rather be a generic role (e.g. table_owner), and the > owner should be the same over all tables of the whole database. This would > work, > too, if I grant membership in role table_owner to all users that may create > tables. > (The users must issue a SET ROLE table_owner before creating tables.) Yes, that will work, but you have to SET ROLE before creating the table. > What I didn't achieve so far is making sure that user_a who created tables in > schema_a > cannot crete/modify tables of schema_b that were created by user_b. Do you > see any way > to achieve this, while still sticking to that generic owner role? No, that is impossible. But I don't understand the motivation: If you want that, why would you want a "table_owner" role? If you don't want user B to be able to drop user A's table, why don't you have each user be the owner of his tables? Yours, Laurenz Albe
Re: Limit transaction lifetime
On 06/03/2020 14:24, Andrei Zhidenkov wrote: Is there a way to limit a transaction lifetime in PostgreSQL? I could use `idle_in_transaction_session_timeout` parameter but it applies only to IDLE transactions. However, I want to rollback any transaction that executes more than specified period of time. -- With best regards, Andrei Zhidenkov. If it's a single command you're trying to limit `SET statement_timeout TO ` should do the trick. If you want it based on the session's cumulative statement time, off the top of my head I can't think of anything in vanilla PG without using executor hooks (that requires some coding). If the queries that worry you are long-lived, you might be able to get by with a scheduled process checking against pg_stat_activity (eg: age(query_start)) and adding the current query's run-time to some per-session total, but it's an highly inaccurate process. -- Regards Fabio Ugo Venchiarutti OSPCFC Network Engineering Dpt. Ocado Technology -- Notice: This email is confidential and may contain copyright material of members of the Ocado Group. Opinions and views expressed in this message may not necessarily reflect the opinions and views of the members of the Ocado Group. If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. References to the "Ocado Group" are to Ocado Group plc (registered in England and Wales with number 7098618) and its subsidiary undertakings (as that expression is defined in the Companies Act 2006) from time to time. The registered office of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.
Re: Real application clustering in postgres.
> > If you have mirrored disks, and you write junk (e.g, because of > a flaw in a fibre channel cable, something I have witnessed), > then you have two perfectly fine copies of the junk. > Few years ago didn't this happen to Salesforce where a firmware bug corrupted the Disk, resulting in corruption of Oracle tablespace blocks and all RAC nodes were equally useless since all of them read from the same disk. Salesforce lost 4 hours worth of data.
Re: What do null column values for pg_stat_progress_vacuum mean?
On 3/5/20 6:39 PM, Mark Haylock wrote: Hi, We have an autovacuum process that has been running for almost 27 hours: SELECT * FROM pg_stat_activity WHERE pid = 11731; -[ RECORD 1 ]+--- datid| 16385 datname | database_name pid | 11731 usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start| 2020-03-04 23:40:14.828138+00 xact_start | 2020-03-04 23:40:14.849367+00 query_start | 2020-03-04 23:40:14.849367+00 state_change | 2020-03-04 23:40:14.849368+00 wait_event_type | wait_event | state| active backend_xid | backend_xmin | 3801997676 query| autovacuum: VACUUM public.responses backend_type | autovacuum worker A row shows up in pg_stat_progress_vacuum, but it contains null values for every column. SELECT * FROM pg_stat_progress_vacuum WHERE pid = 11731; -[ RECORD 1 ]--+--- pid| 11731 datid | 16385 datname| d2j496215lfs41 relid | phase | heap_blks_total| heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples| num_dead_tuples| I see nothing in the documentation to suggest that this is an expected state - what does it mean? Not sure, but you might try: https://www.postgresql.org/docs/10/view-pg-locks.html The pid column can be joined to the pid column of the pg_stat_activity view to get more information on the session holding or awaiting each lock, for example SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid; Thanks, Mark. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Limit transaction lifetime
> If it's a single command you're trying to limit `SET statement_timeout TO > ` should do the trick. This will set only statement timeout but won’t work for long transactions that contain a lot of short statements. > If you want it based on the session's cumulative statement time, off the top > of my head I can't think of anything in vanilla PG without using executor > hooks (that requires some coding). Yes, that’s exactly I want to do. > If the queries that worry you are long-lived, you might be able to get by > with a scheduled process checking against pg_stat_activity (eg: > age(query_start)) and adding the current query's run-time to some per-session > total, but it's an highly inaccurate process. I think in my case I should check `xact_start`, because not every query initiates a new transaction.
Re: Limit transaction lifetime
On 06/03/2020 16:14, Andrei Zhidenkov wrote: If it's a single command you're trying to limit `SET statement_timeout TO ` should do the trick. This will set only statement timeout but won’t work for long transactions that contain a lot of short statements. If you want it based on the session's cumulative statement time, off the top of my head I can't think of anything in vanilla PG without using executor hooks (that requires some coding). Yes, that’s exactly I want to do. If the queries that worry you are long-lived, you might be able to get by with a scheduled process checking against pg_stat_activity (eg: age(query_start)) and adding the current query's run-time to some per-session total, but it's an highly inaccurate process. I think in my case I should check `xact_start`, because not every query initiates a new transaction. That way you'd also be counting in time a given transaction spent idling, which IME is one of the biggest source of concurrency headaches if it's holding locks while doing nothing. If your use case is unaffected by that, that'd be good news for you. -- Regards Fabio Ugo Venchiarutti OSPCFC Network Engineering Dpt. Ocado Technology -- Notice: This email is confidential and may contain copyright material of members of the Ocado Group. Opinions and views expressed in this message may not necessarily reflect the opinions and views of the members of the Ocado Group. If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. References to the "Ocado Group" are to Ocado Group plc (registered in England and Wales with number 7098618) and its subsidiary undertakings (as that expression is defined in the Companies Act 2006) from time to time. The registered office of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.
Re: Real application clustering in postgres.
On 3/6/20 8:55 AM, Laurenz Albe wrote: On Fri, 2020-03-06 at 03:25 -0600, Ron wrote: RAC is not really a high availability solution: because of the shared storage, it has a sibgle point of failure. This is utter nonsense. Dual redundant storage controllers connected to disks in RAID-10 configurations have been around for at least 25 years. Oracle got it's clustering technology from DEC, and I know that works. Cluster members, storage controllers and disks have all gone down, while the database and application keep on humming along. I am not saying that it is buggy, it is limited by design. If you have mirrored disks, and you write junk (e.g, because of a flaw in a fibre channel cable, something I have witnessed), then you have two perfectly fine copies of the junk. Why do you have just one FC path? I am not saying the (physical) disk is the single point of failure, the (logical) file system is (Oracle calls it ASM / tablespace, but it is still a file system). Why isn't the filesystem (or RDBMS) throwing checksum errors? This was standard stuff in legacy Enterprise RDBMSs 20 years ago. -- Angular momentum makes the world go 'round.
Re: Real application clustering in postgres.
Yup, if you need true shared storage, Oracle RAC is still the only solution out there, All the other multi-master solutions work by capturing the sql statements themselves. And properly configured it RAC is indeed part of an HA solution. Any time you have everything in a single data center, as you do with basic RAC, you do have a potential single point of failure however, a tornado for example can take out the entire data center. There are solutions like stretch RAC that can account for that issue, as well as data guard. Oracle also has a nice DR feature called Dataguard, which creates an exact duplicate of your running database..The nice point of oracle dataguard is that it is a block by block copy, while all of the Postgres Multi-Master and master-slave replication solutions work by SQL capture. Potentially DRDB could be used to set up a standby database for postgres, though it is considerably more involved to create than Oracle data guard. With the advent of virtualization and the concomitant ease of adding CPU and memory to a running database instance, the use cases for true Oracle RAC are much less than in the past, scalability is no longer nearly the issue it used to be, and with the speed of failover at the virtual guest level, the HA requirements are much less substantial than in the past. There are probably still some use cases where the down time required for patching is not acceptable, in which case RAC is still required. The same is true for Postgres or any other Multi-Master instances of course, unless the intent of clustering/multi-master is availability instead of scalability, there isnt much point considering the ease of adding resources to virtual servers. On Fri, Mar 6, 2020 at 10:56 AM Ron wrote: > On 3/6/20 8:55 AM, Laurenz Albe wrote: > > On Fri, 2020-03-06 at 03:25 -0600, Ron wrote: > >>> RAC is not really a high availability solution: because of the shared > >>> storage, it has a sibgle point of failure. > >> This is utter nonsense. Dual redundant storage controllers > >> connected to disks in RAID-10 configurations have been around for at > >> least 25 years. > >> > >> Oracle got it's clustering technology from DEC, and I know > >> that works. Cluster members, storage controllers and disks have all > >> gone down, while the database and application keep on humming along. > > I am not saying that it is buggy, it is limited by design. > > > > If you have mirrored disks, and you write junk (e.g, because of > > a flaw in a fibre channel cable, something I have witnessed), > > then you have two perfectly fine copies of the junk. > > Why do you have just one FC path? > > > I am not saying the (physical) disk is the single point of failure, the > > (logical) file system is (Oracle calls it ASM / tablespace, but it is > > still a file system). > > Why isn't the filesystem (or RDBMS) throwing checksum errors? This was > standard stuff in legacy Enterprise RDBMSs 20 years ago. > > -- > Angular momentum makes the world go 'round. > > > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'
Re: Real application clustering in postgres.
On 3/6/20 01:25, Ron wrote: > On 3/5/20 6:07 AM, Laurenz Albe wrote: >> On Thu, 2020-03-05 at 07:45 +, Daulat Ram wrote: >>> Is there any possibility/options to setup a real application clustering in >>> Postgres as in Oracle we have a RAC feature. >> No, and as far as I know nobody feels interested in providing it. >> >> RAC is a complicated architecture that doesn't do much good, so most >> people feel that it would be a waste of time and effort. >> >> RAC ist not really a scaling solution: because of the shared storage, >> you can only scale for more CPUs; I/O remains the bottleneck. >> RAC is not really a high availability solution: because of the shared >> storage, it has a sibgle point of failure. > > This is utter nonsense. Dual redundant storage controllers connected to > disks in RAID-10 configurations have been around for *at least* 25 years. > > Oracle got it's clustering technology from DEC, and I *know* that > works. Cluster members, storage controllers and disks have all gone > down, while the database and application keep on humming along. Just want to point out that while the merits of RAC are considered as universal truth in the Oracle marketing community, they are certainly still debated and nuanced within the Oracle technical community. Some great reading would be Mogens Norgaard's article "You Probably Don't Need RAC" from 2003 (which is surprisingly hard to find on the internet) and much of the ensuing discussion between then and now, for example this recent message over on the oracle-l list: https://www.freelists.org/post/oracle-l/Chuckleworthy-issue-of-the-NoCOUG-Journal,1 For my part, I spent many years running RAC clusters and solving the usual handful of problems you bump into, and I definitely have a strong bias now toward as simple of architectures as possible. Regardless of which parties participate in your operations, and regardless of who owns the data center where your stack is running. Note that I apply this to all the new databases as well. I enjoy and appreciate the opportunities I've had to work on some really interesting new technology - but I also still see merit in simple, boring, mature, well-understood architectures if they are doing the job. Complexity will find us all soon enough driven by true business needs without being helped by the pursuit of shiny things! It seemed to me there was a major and well-funded effort to market and sell cluster databases for many years, and as a result I suspect that while there are certainly some good use cases, there are probably also some people using RAC today who would do fine (or better) without it. Seems I even touched on this all the way back in 2007 in an article on my own blog... https://ardentperf.com/2007/05/10/fidelity-case-study/ -Jeremy -- http://about.me/jeremy_schneider
Re: Is it safe to rename an index through pg_class update?
Hi, On 2020-02-27 10:52:36 -0500, Tom Lane wrote: > FWIW, I can't immediately think of a reason this would cause a problem, > at least not on 9.4 and up which use MVCC catalog scans. If you're > really still on 9.3 then it's notably more risky. In any case, I've > not had any caffeine yet today, so this doesn't count for much. It likely could cause some problems if somebody concurrently executed DDL affecting the same table. At least some "concurrently updated" errors, and perhaps some worse ones. I'd at least add an explicit LOCK TABLE on the underlying table that prevents concurrent catalog modifications. Greetings, Andres Freund
libpq and escaping array string literals
I've got so C code that interacting with a table containing a field of type text[]. Strings I've got to put in the array may be unicode and or contain single or double quotes etc ... What's the best way to escape these strings? Ted
Re: libpq and escaping array string literals
Hey Ted, libpq has no such a facility. If you're on C++ you may want to use Pgfe library to work with the database arrays easy. On Fri, 6 Mar 2020, 22:11 Ted Toth, wrote: > I've got so C code that interacting with a table containing a field of > type text[]. Strings I've got to put in the array may be unicode and or > contain single or double quotes etc ... What's the best way to escape these > strings? > > Ted >
Restrict user to create only one db with a specific name
Hi, Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name? For example, userA can only create a database with a name called mydb. Any other names would generate an error. If that is not possible, will it be possible then to limit userA to creating only one database? Granting the createdb privilege would allow the user to create any number of databases which I want to avoid. Thanks. Tiff
Re: Restrict user to create only one db with a specific name
On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang wrote: > Hi, > Is there a way in PostgreSQL 12 to restrict user to creating a database > with a specific database name? > > For example, userA can only create a database with a name called mydb. Any > other names would generate an error. > > If that is not possible, will it be possible then to limit userA to > creating only one database? Granting the createdb privilege would allow the > user to create any number of databases which I want to avoid. > No. Though you could prevent them from being able to connect to unexpected databases via the pg_hba.conf file. Why does userA need create database privileges? You could just have them log into an admin database and run a function that creates the database for them using function owner privileges then you can add whatever special logic you want to that function. David J.
Re: Restrict user to create only one db with a specific name
On Friday, March 6, 2020, David G. Johnston wrote: > On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang > wrote: > >> Hi, >> Is there a way in PostgreSQL 12 to restrict user to creating a database >> with a specific database name? >> >> > >> You could just have them log into an admin database and run a function >> that creates the database for them using function owner privileges then you >> can add whatever special logic you want to that function. >> > Or not...the transaction involved with executing a function causes create database to not work... David J.
Re: Restrict user to create only one db with a specific name
Hi Tiff, from what you say, it sounds that each user should have his or her own database. Considering the other answers here already pointing out the difficulties, why don't you just create a database for each user with the same name as the username and grant him or her access to it. So, basically like this: postgres=# create role "userA" login; CREATE ROLE postgres=# create database "userA" owner "userA"; CREATE DATABASE postgres=# create role "userB" login; CREATE ROLE postgres=# create database "userB" owner "userB"; CREATE DATABASE When, say, "userB" goes away, his or her data will go the way all things do: drop database "userB"; drop role "userB"; Or did I misunderstand you? Cheers, Paul > On 07. Mar, 2020, at 00:28, Tiffany Thang wrote: > > Hi, > Is there a way in PostgreSQL 12 to restrict user to creating a database with > a specific database name? > > For example, userA can only create a database with a name called mydb. Any > other names would generate an error. > > If that is not possible, will it be possible then to limit userA to creating > only one database? Granting the createdb privilege would allow the user to > create any number of databases which I want to avoid. > > Thanks. > > Tiff
