Re: PostgreSQL 9.2 high replication lag
> Version 9.2 is very old and has a lot of issues related to streaming > replication. Additionally, it is already EOL. Agreed. > Furthermore, max_standby_streaming_delay has no relation with the streaming > lag. It's associated with queries conflicting with data. This parameter will > not speed up the WAL apply operation. Yes, I know. But this parameter determines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries. So, there could be lots of queries conflicting with about-to-be-applied WAL entries. Again, I was not sure about what is causing the delay so I needed to try any possibility. > Kindly consider upgrading to a supported version. Yes. We have a project in progress to upgrade. On top of that, I can say that I have restarted the slave and the replication is now normal. [image.png] You can see above that the replication delay is not passing 3 minutes, which is the max_standby_streaming_delay, but the actual average is 5 seconds. So, looks like rebooting the EC2 instance resolved the issue, but I do not know why. publickey - root@sud0.nz - 0xC5E964A1.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
View of Union Multiple Tables - Only If table EXISTS
Hi, I would like to create a view which is UNION of multiple tables with the same name from different schemas. Select col1, col2 from schemaA.table UNION Select col1, col2 from schemaB.table However, it is possible that in some of the schemas the table was not created yet. I know I can check in which schemas the table exists and then create the view, but at any given time the table can be added to other schemas. In that case, I will always need to check if the view is up-to-date before accessing it and this makes the view useless. Is it possible to write the view syntax once and it will do some type of SELECT IF EXISTS. Thanks Avi IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
Re: View of Union Multiple Tables - Only If table EXISTS
On Sunday, July 25, 2021, Avi Weinberg wrote: > > Is it possible to write the view syntax once and it will do some type of > SELECT IF EXISTS. > > > Not with a virew. Dynamic SQL in a function can be used to build a query at runtime that includes only tables that exist. David J.
Re: pg_restore (fromuser -> touser)
Thanks for your reply. Is this something that I can request as a feature add? I don't think it should be too much of effort (based on my limited source code knowledge), but I'm not familiar with the process to request a feature. Thanks, Mayan On Fri, Jul 23, 2021, 10:58 PM Ninad Shah wrote: > This is true. Such a feature is not available in PostgreSQL. > > What you need to do is you have to take a structure dump, and change the > schema name as per required. And, then, you may copy the data. > > > Regards, > Ninad Shah > > On Fri, 23 Jul 2021 at 23:08, Mayan wrote: > >> Hi, >> >> >> >> I had a general question about a feature that we depended on heavily when >> using other RDBMS providers which was the ability to take a schema dump and >> restore it to a different database and a different schema in that database >> (could be to the same database as well). Basically, there was an option >> on restore to specify a FROMUSER and a TOUSER directive so schema A could >> be restored elsewhere but as schema B. >> >> >> >> I don’t see such an option in Postgres and the only workaround I am aware >> of is to do a plain-text (format=p) and then a crude find/replace to >> replace the old schema name with the new schema name. I’ve never >> actually tested this to be sure even this would work. Also, using this >> option will prevent us from parallelizing the import or export, so it’s >> really not something we want to do. >> >> >> >> This would be a really useful feature in my opinion along with the >> ability to maintain parallelization options. >> >> >> >> Are there any such features on the roadmap? Is my understanding correct >> about the available ways to accomplish this – again, in a practical and >> performant way? >> >> >> >> Thanks, >> >> Mayan >> >
regarding sql password auth
I have just downloaded the postgresql .In the SQLshell i am not able to go ahead as password is not authenticated . Can you tell me what to do next ? Below is the Screenshot ! If possible send a solution asap.
Re: regarding sql password auth
> I have just downloaded the postgresql .In the SQLshell i am not able to go > ahead as password is not authenticated . > I am not too well versed with windows installer, but I know during installation it asks for admin username and password. The same cred should work with initial login. https://www.postgresqltutorial.com/install-postgresql/ >
Re: pg_restore (fromuser -> touser)
On Sun, 25 Jul 2021 at 21:09, Mayan wrote: > Thanks for your reply. Is this something that I can request as a feature > add? I don't think it should be too much of effort (based on my limited > source code knowledge), but I'm not familiar with the process to request a > feature. > although there is not flag in command line, but you always run the alter rename to for role and schema post restore. i can be corrected, if i am missing anything. i tried with pagilla db, but just keeping it simple here. PS: pg_hba.conf needs to be updated with new role/user post restore, or else user will not be able to connect. postgres@db:~/playground/demo$ createdb demo postgres@db:~/playground/demo$ createuser demo postgres@db:~/playground/demo$ pg_restore -d demo demo.db -- some dummy restore which has objects owned by demo user in demo schema postgres@db:~/playground/demo$ psql demo psql (14beta1) Type "help" for help. demo=# \dt demo.* List of relations Schema | Name | Type | Owner +--+---+--- demo | t| table | demo (1 row) demo=# \ds demo.t_id_seq List of relations Schema | Name | Type | Owner +--+--+--- demo | t_id_seq | sequence | demo (1 row) demo=# \df demo.* List of functions Schema | Name| Result data type | Argument data types | Type +---+--+-+-- demo | trig_func | trigger | | func (1 row) demo=# \dnS demo List of schemas Name | Owner --+--- demo | demo (1 row) ---the below to sql statements will probably do what you want in your restore command line flag, you need not edit dump file by hand imho *demo=# alter schema demo rename to production;* *ALTER SCHEMA* *demo=# alter role demo rename to production;* *ALTER ROLE* --validate demo=# \dnS demo -- no more demo schema List of schemas Name | Owner --+--- (0 rows) demo=# \dnS List of schemas Name| Owner + information_schema | postgres pg_catalog | postgres pg_toast | postgres production | production public | postgres (5 rows) demo=# \dt production.* List of relations Schema | Name | Type | Owner +--+---+ production | t| table | production (1 row) demo=# \df production.trig_func List of functions Schema | Name| Result data type | Argument data types | Type +---+--+-+-- production | trig_func | trigger | | func (1 row) demo=# \ds production.t_id_seq List of relations Schema | Name | Type | Owner +--+--+ production | t_id_seq | sequence | production (1 row)
Re: pg_restore (fromuser -> touser)
On Sun, Jul 25, 2021 at 8:39 AM Mayan wrote: > Thanks for your reply. Is this something that I can request as a feature > add? I don't think it should be too much of effort (based on my limited > source code knowledge), but I'm not familiar with the process to request a > feature. > You just did. But unless you are going to fund or help with actual development I don't see this going very far. This has been inquired about many times in the past yet the feature still does not exist. David J.
Re: pg_restore (fromuser -> touser)
You just need to export dump without any privileges. And while restoring dump use the new role. Regards, Ganesh Korde. On Mon, 26 Jul 2021, 1:01 am David G. Johnston, wrote: > On Sun, Jul 25, 2021 at 8:39 AM Mayan wrote: > >> Thanks for your reply. Is this something that I can request as a feature >> add? I don't think it should be too much of effort (based on my limited >> source code knowledge), but I'm not familiar with the process to request a >> feature. >> > > You just did. But unless you are going to fund or help with actual > development I don't see this going very far. This has been inquired about > many times in the past yet the feature still does not exist. > > David J. > >
Re: pg_restore (fromuser -> touser)
On Sun, Jul 25, 2021 at 8:02 PM Ganesh Korde wrote: > You just need to export dump without any privileges. And while restoring > dump use the new role. > You should read the body of the original email and not just the subject line. The actual question pertains to schemas - which IIUC are tightly linked to roles in other DBs (hence the observed behavior elsewhere) but aside from some default search_path stuff are unrelated in PostgreSQL. David J.
PostgreSQL reference coffee mug
Hello, Nearly 20 years ago, I ordered some 50 vi-reference coffee mugs like this one here (not exactly the same, but to give you an idea): https://www.getdigital.eu/vi-reference-mug.html for our vi-lovers in-house and the admins of our customers. I'd like to do the same now with a reference mug for PostgreSQL, for example with the most important Meta-commands, sys tables, etc. I have nearby a copy shop which would produce it based on a JPEG file. Before designing our own, I wanted to ask here if such a beast exists already or, if not, maybe even a Reference Card in PDF or JPEG to use it as a starting point. Thanks in advance for any hints. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
