Re: Supported RHEL version for PostgreSQL 11

[Justin Pryzby]

On Sat, Aug 28, 2021 at 04:08:33PM +0800, Sudhir Guna wrote:
> The RHEL upgrade from 7.5 to 8.4 is not done as an incremental version
> upgrade rather directly from 7.5.

I think the process should be:

1) upgrade from RH7 to RH8;
2) reindex relevant indexes (see wiki link below);
3) upgrade from postgres 9.6 to 11;

You may need to install updated PGDG packages for the RH8.

How are you planning to upgrade postgres ?
Using pg_upgrade, or pg_dump, or ??

> Postgres is installed using PGDG packages.
>
> On Thu, Aug 26, 2021 at 1:15 AM Justin Pryzby  wrote:
> 
> > On Wed, Aug 25, 2021 at 11:47:57PM +0800, Sudhir Guna wrote:
> > >  Hi All,
> > >
> > > I would like to clarify on OS upgrade for PostgreSQL.
> > >
> > > Currently we are on PostgreSQL 9.6 on RHEL 7.5. We would like to do a
> > > direct upgrade from RHEL 7.5 to RHEL 8.4 and then upgrade PostgreSQL 9.6
> > to
> > > PostgreSQL 11.
> > >
> > > Is it ok to do a direct upgrade from RHEL 7.5 to RHEL 8.4
> >
> > Please describe what you mean by a "direct upgrade" ?
> >
> > You should be careful, since the sort order of indexes can change:
> > https://wiki.postgresql.org/wiki/Locale_data_changes
> > | Version 8 uses the new locale data. Therefore, caution will be necessary
> > when upgrading.
> >
> > > Is PostgreSQL 11 compatible with RHEL 8.4
> >
> > How did you install postgres ?
> > Compiled from source ?
> > Using RHEL packages ?
> > Using PGDG packages ?
> >
> > PDDG has pacakges for PG11/RH8
> > https://yum.postgresql.org/rpmchart/#pg11

Re: Please help: pgAdmin 4 on Amazon Linux 2

[Tiemen Ruiten]

Hi,

Amazon Linux is not compatible with RHEL the way CentOS is and it's not a
supported platform for the RPM installation:
https://www.pgadmin.org/download/pgadmin-4-rpm/

The repo configuration probably uses some variables to create the download
URLs and because the versions don't match, it results in a 404. You may
have some luck downloading and installing the RPM directly, but you'll
likely want to take a look at other installation methods on the page I
linked if you want to install pgAdmin on Amazon Linux.

On Fri, Aug 27, 2021 at 5:34 PM Blake McBride  wrote:

> Greetings,
>
> I am trying to install pgAdmin 4 on Amazon Linux 2.  PostgreSQL is already
> installed and working fine.  I believe Amazon Linux 2 is based on RedHat.
>
> I am doing the following:
>
> [root@a-1lxumlkkw4mu4 ~]# rpm -i
> https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
> warning: /var/tmp/rpm-tmp.ZEygli: Header V3 RSA/SHA256 Signature, key ID
> 210976f2: NOKEY
> [root@a-1lxumlkkw4mu4 ~]# yum install pgadmin4
> Loaded plugins: amzn_workspaces_filter_updates, halt_os_update_check,
> priorities, update-motd
> amzn2-core
>   | 3.7 kB  00:00:00
>
> amzn2extra-GraphicsMagick1.3
>   | 3.0 kB  00:00:00
>
> amzn2extra-docker
>| 3.0 kB  00:00:00
>
> amzn2extra-epel
>| 3.0 kB  00:00:00
>
> amzn2extra-gimp
>| 1.3 kB  00:00:00
>
> amzn2extra-libreoffice
>   | 3.0 kB  00:00:00
>
> amzn2extra-mate-desktop1.x
>   | 3.0 kB  00:00:00
>
> epel/x86_64/metalink
>   |  15 kB  00:00:00
>
> firefox
>| 2.2 kB  00:00:00
>
> google-chrome
>| 1.3 kB  00:00:00
>
>
> https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-2-x86_64/repodata/repomd.xml:
> [Errno 14] HTTPS Error 404 - Not Found
> Trying other mirror.
>
>
>  One of the configured repositories failed (pgadmin4),
>  and yum doesn't have enough cached data to continue. At this point the
> only
>  safe thing yum can do is fail. There are a few ways to work "fix" this:
>
>  1. Contact the upstream for the repository and get them to fix the
> problem.
>
>  2. Reconfigure the baseurl/etc. for the repository, to point to a
> working
> upstream. This is most often useful if you are using a newer
> distribution release than is supported by the repository (and the
> packages for the previous distribution release still work).
>
>  3. Run the command with the repository temporarily disabled
> yum --disablerepo=pgAdmin4 ...
>
>  4. Disable the repository permanently, so yum won't use it by
> default. Yum
> will then just ignore the repository until you permanently enable
> it
> again or use --enablerepo for temporary usage:
>
> yum-config-manager --disable pgAdmin4
> or
> subscription-manager repos --disable=pgAdmin4
>
>  5. Configure the failing repository to be skipped, if it is
> unavailable.
> Note that yum will try to contact the repo. when it runs most
> commands,
> so will have to try and fail each time (and thus. yum will be be
> much
> slower). If it is a very temporary problem though, this is often a
> nice
> compromise:
>
> yum-config-manager --save
> --setopt=pgAdmin4.skip_if_unavailable=true
>
> failure: repodata/repomd.xml from pgAdmin4: [Errno 256] No more mirrors to
> try.
>
> https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-2-x86_64/repodata/repomd.xml:
> [Errno 14] HTTPS Error 404 - Not Found
> [root@a-1lxumlkkw4mu4 ~]#
>
>
> I have no idea how to fix this.  Any help would sure be appreciated.
>
> Blake McBride
>
>


-- 
Tiemen Ruiten
Infrastructure Engineer

Re: Can we get rid of repeated queries from pg_dump?

[Tom Lane]

hubert depesz lubaczewski  writes:
> On Fri, Aug 27, 2021 at 05:23:23PM -0400, Tom Lane wrote:
>> Those queries are coming from getFormattedTypeName(), which is used
>> for function arguments and the like.  I'm not quite sure why Hubert
>> is seeing 5000 such calls in a database with only ~100 functions;
>> surely they don't all have an average of 50 arguments?

> 23 functions with 0 arguments, 52 with 1, and the max is 5 arguments - two
> functions have these.
> Not sure if it matters but there is a lot of enums. 83 of them. And they have
> up to 250 elements (2 such types).

Hmm, no, I don't see any getFormattedTypeName calls in dumpEnumType.

There are two of 'em in dumpCast though.  Does this DB by chance
have a ton of user-defined casts?

regards, tom lane

Re: Unexpected block ID found when reading data

[Adrian Klaver]

On 8/27/21 5:31 PM, Gilar Ginanjar wrote:

Hi, sorry it's been a long time to reply.

It throw the same errors. I can't find any solution. It frustrated me 
and i've been on a break since. ;)



Per upstream advice do something like:

pg_restore -U myuser -s -d mydb dbdump.backup

to get the schema definitions into the database.

Then:

pg_restore -U myuser -a -t  -d mydb dbdump.backup

to restore the data for a  table at a time.

You will mostly likely hit an error on one or more tables, but you will 
get the rest in and know where your problem is.





On 5 Aug 2021, at 00.50, Vijaykumar Jain 
> wrote:


On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar 
mailto:gi...@innovation-project.com>> 
wrote:


I’m not sure which patch version i used to dump, but i was using
postgre 12.5 for pg_dump back then.

I’m running pg_restore -f dbdump.backup right now, I think it will
take some times because it has a large size (around 9 GB). There
are no issues yet.


Did this complete without issues ? or did it throw the same errors ?





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

Re: use fopen unknown resource

[Adrian Klaver]

On 8/27/21 3:50 PM, ourdiaspora wrote:


‐‐‐ Original Message ‐‐‐

On Friday, August 27th, 2021 at 11:10 PM, Adrian Klaver 
 wrote:


https://www.php.net/manual/en/pdo.pgsqlcopyfromfile.php



"
public PDO::pgsqlCopyFromFile(
 string $table_name,
 string $filename,
"

Sorry but do not understand; the line does not explain what to write in the php 
file.


I'm not a PHP programmer, but I'm going to say it is filename as 
described here:


https://www.php.net/manual/en/function.fopen.php

" filename

If filename is of the form ...
"



The plan is to write an html file for a user to select a csv file to import 
into a database. The manual suggests that the file name is already known (e.g. 
https://www.php.net/manual/en/function.fgetcsv.php)


You are asking the user to select a file, so there should be some sort 
of file reference at that point, correct?


Same for below.



Instead of:
"
...
fopen("test.csv", "r"))
...
"

it would _not_ be possible to write, correct?:
"
...
fopen("", "r"))





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

Re: use fopen unknown resource

[Peter J. Holzer]

On 2021-08-28 09:52:45 -0700, Adrian Klaver wrote:
> On 8/27/21 3:50 PM, ourdiaspora wrote:
> > On Friday, August 27th, 2021 at 11:10 PM, Adrian Klaver 
> >  wrote:
> > 
> > > https://www.php.net/manual/en/pdo.pgsqlcopyfromfile.php
> > > 
> > 
> > "
> > public PDO::pgsqlCopyFromFile(
> >  string $table_name,
> >  string $filename,
> > "
> > 
> > Sorry but do not understand; the line does not explain what to write in the 
> > php file.
> 
> I'm not a PHP programmer, but I'm going to say it is filename as described
> here:

The use of PHP suggests that this is a web application. So most likely
the user is supposed to upload the file via an HTML form. I would
suggest to the OP to learn how to process file uploads in PHP. Once
they've figured that out it will probably be clear what file name to
use.

hp

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


signature.asc
Description: PGP signature

Re: use fopen unknown resource

[ourdiaspora]

On Saturday, August 28th, 2021 at 5:52 PM, Adrian Klaver 
 wrote:

>
> You are asking the user to select a file, so there should be some sort
>
> of file reference at that point, correct?
>

This is what causes confusion. If a user has a file named 'mydatafile.csv', why 
does the manual make reference to open 'test.csv'???:

https://www.php.net/manual/en/function.fgetcsv.php
"
...
if (($handle = fopen("test.csv", "r")) !== FALSE) {
...
"

Re: use fopen unknown resource

[David G. Johnston]

On Fri, Aug 27, 2021 at 2:59 PM ourdiaspora 
wrote:

> Please what is the syntax to assign an unknown file name to the 'fopen'
> function?
>
>
There isn't one as it would make no sense - how is it supposed to open a
file if there is no filename provided to open?

You have to set things up yourself so that the content you want to open
exists in a file at a known location and then pass that location to the
function.  In particular, in a web server context, that means taking the
request input from the client that represents a file and saving it
somewhere first - then opening that saved content.  If the user is
supplying a suggested file name for the content you can incorporate that.
You can also ignore it and just make something up.  It doesn't matter
(other than concerns about overwriting existing files).  But whatever name
you choose to give to the newly created file on the server is the one you
pass to fopen.  The name/location of the file on the client machine is
irrelevant - the server cannot even see that machine (or, at least should
not care even if it technically can) per the fundamental architectural
principle of "client-server" design.

David J.

Re: Can we get rid of repeated queries from pg_dump?

[Tom Lane]

Here is a second patch, quite independent of the first one, that
gets rid of some other repetitive queries.  On the regression database,
the number of queries needed to do "pg_dump -s regression" drops from
3260 to 2589, and on my machine it takes 1.8 sec instead of 2.1 sec.

What's attacked here is a fairly silly decision in getPolicies()
to query pg_policy once per table, when we could do so just once.
It might have been okay if we skipped the per-table query for
tables that lack policies, but it's not clear to me that we can
know that without looking into pg_policy.  In any case I doubt
this is ever going to be less efficient than the original coding.

regards, tom lane

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6adbd20778..befe68de1a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3656,7 +3656,7 @@ dumpBlobs(Archive *fout, const void *arg)
 
 /*
  * getPolicies
- *	  get information about policies on a dumpable table.
+ *	  get information about all RLS policies on dumpable tables.
  */
 void
 getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
@@ -3666,6 +3666,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
 	PolicyInfo *polinfo;
 	int			i_oid;
 	int			i_tableoid;
+	int			i_polrelid;
 	int			i_polname;
 	int			i_polcmd;
 	int			i_polpermissive;
@@ -3681,6 +3682,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
 
 	query = createPQExpBuffer();
 
+	/*
+	 * First, check which tables have RLS enabled.  We represent RLS being
+	 * enabled on a table by creating a PolicyInfo object with null polname.
+	 */
 	for (i = 0; i < numTables; i++)
 	{
 		TableInfo  *tbinfo = &tblinfo[i];
@@ -3689,15 +3694,6 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
 		if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
 			continue;
 
-		pg_log_info("reading row security enabled for table \"%s.%s\"",
-	tbinfo->dobj.namespace->dobj.name,
-	tbinfo->dobj.name);
-
-		/*
-		 * Get row security enabled information for the table. We represent
-		 * RLS being enabled on a table by creating a PolicyInfo object with
-		 * null polname.
-		 */
 		if (tbinfo->rowsec)
 		{
 			/*
@@ -3719,51 +3715,35 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
 			polinfo->polqual = NULL;
 			polinfo->polwithcheck = NULL;
 		}
+	}
 
-		pg_log_info("reading policies for table \"%s.%s\"",
-	tbinfo->dobj.namespace->dobj.name,
-	tbinfo->dobj.name);
-
-		resetPQExpBuffer(query);
-
-		/* Get the policies for the table. */
-		if (fout->remoteVersion >= 10)
-			appendPQExpBuffer(query,
-			  "SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, "
-			  "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
-			  "   pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, "
-			  "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
-			  "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
-			  "FROM pg_catalog.pg_policy pol "
-			  "WHERE polrelid = '%u'",
-			  tbinfo->dobj.catId.oid);
-		else
-			appendPQExpBuffer(query,
-			  "SELECT oid, tableoid, pol.polname, pol.polcmd, 't' as polpermissive, "
-			  "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
-			  "   pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, "
-			  "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
-			  "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
-			  "FROM pg_catalog.pg_policy pol "
-			  "WHERE polrelid = '%u'",
-			  tbinfo->dobj.catId.oid);
-		res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+	/*
+	 * Now, read all RLS policies, and create PolicyInfo objects for all those
+	 * that are of interest.
+	 */
+	pg_log_info("reading row-level security policies");
 
-		ntups = PQntuples(res);
+	printfPQExpBuffer(query,
+	  "SELECT oid, tableoid, pol.polrelid, pol.polname, pol.polcmd, ");
+	if (fout->remoteVersion >= 10)
+		appendPQExpBuffer(query, "pol.polpermissive, ");
+	else
+		appendPQExpBuffer(query, "'t' as polpermissive, ");
+	appendPQExpBuffer(query,
+	  "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
+	  "   pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, "
+	  "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
+	  "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
+	  "FROM pg_catalog.pg_policy pol");
 
-		if (ntups == 0)
-		{
-			/*
-			 * No explicit policies to handle (only the default-deny policy,
-			 * which is handled as part of the table definition).  Clean up
-			 * and 

Re: use fopen unknown resource

[Adrian Klaver]

On 8/28/21 2:55 PM, ourdiaspora wrote:


On Saturday, August 28th, 2021 at 5:52 PM, Adrian Klaver 
 wrote:



You are asking the user to select a file, so there should be some sort

of file reference at that point, correct?



This is what causes confusion. If a user has a file named 'mydatafile.csv', why 
does the manual make reference to open 'test.csv'???:


Because that is a 'dummy' file name for the purposes of illustrating 
what sort of information needs to be provided. Just substitute in 
whatever file name is actually being fetched in the production code.




https://www.php.net/manual/en/function.fgetcsv.php
"
...
if (($handle = fopen("test.csv", "r")) !== FALSE) {
...
"






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

Insert statement doesn't complete

[Trang Le]

Hi all,

I am facing an issue.

when I run a script:
INSERT INTO mdm.etl_exception_info
SELECT
*
FROM mdm.etl_exception_info_vw2;
after running script I check this script in pg_lock, everything ok, job
done, data is inserted. However, the session contains the script above
doesn't complete though data is inserted and job done that is verified in
pg_lock.

Could you help me on this issue?

Thanks all.

Regards,
Trang

Re: Insert statement doesn't complete

[David G. Johnston]

On Saturday, August 28, 2021, Trang Le  wrote:

>
> after running script
>
>
How?


>
>  I check this script in pg_lock, everything ok, job
> done, data is inserted.
>
>
You can tell this from pg_lock?


>
>
>  However, the session
>
>
Where is this session?


David J.

Re: Insert statement doesn't complete

[Mladen Gogala]

On 8/28/21 11:40 PM, Trang Le wrote:

Hi all,

I am facing an issue

when I run a script:
INSERT INTO mdm.etl_exception_info
SELECT
        *
FROM mdm.etl_exception_info_vw2;


Is there any error message thrown? If there is, can you tell us? Is 
autocommit on? What does the log say?



after running script I check this script in pg_lock, everything ok, job
done, data is inserted. However, the session contains the script above
doesn't complete though data is inserted and job done that is verified in
pg_lock.


What does the phrase "the script above doesn't complete" mean? How do 
you know that the script doesn't complete? Is the script hanging?  
Furthermore, what is the software version? What platform are you using?




Could you help me on this issue?


Insufficient data for a meaningful answer.




Thanks all.

Regards,
Trang


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