About postgres pg_upgrade
Hi Team, Could you please explain me how does pg_upgrade works one of my friends it works based on pg_restore I am bit confused
Re: About postgres pg_upgrade
> On 5 Apr 2024, at 06:47, Rama Krishnan wrote: > Could you please explain me how does pg_upgrade works one of my friends it > works based on pg_restore I am bit confused The documentation does a fairly good job explaining how it works, and there are multiple blogposts and presentations on the subject to be found. https://www.postgresql.org/docs/devel/pgupgrade.html To summarize in a few words, pg_upgrade does a dump/restore of the schema from the old cluster into the new, but copies the table data. Your friend is thus isn't wrong, it does use pg_restore but only for the schema, not the data. -- Daniel Gustafsson
Re: Failure of postgres_fdw because of TimeZone setting
Hi Tom, thank you for your reply! Tom Lane wrote: You realize of course that PG 9.4.x is four years past EOL, and that the last release in that series was 9.4.26, so that your remote is missing three or so years' worth of bug fixes even before its EOL. The underlying macOS platform looks a bit hoary as well. Yes, but this is really out of my hands. My task is to perform analysis on the remote database and my current attempt is to interface with the data using postgres_fdw in my own database, where I can put views and functions. (You gain exactly zero points for good maintenance practice on the local side either, since PG 13's current release is 13.14. If you're going to install Postgres and then ignore bug-fix releases for multiple years, I counsel not starting from a dot-zero release. However, that doesn't seem to be related to your immediate problem.) Agreed. :-) I inherited this docker container and am in the process of getting it updated as we speak. My best guess is that the remote was built with a --with-system-tzdata setting that's not actually valid for its platform. Interesting! From what I gathered, I can check the options that were used by running `pg_config --configure`. Indeed, it appears that the remote compiled their own Postgres and this is the output of the above command: '--prefix=/Applications/...' '--with-includes=/Applications/.../libopenssl/include:/Applications/.../libxml2/include' '--with-libraries=/Applications/.../libopenssl/lib:/Applications/.../libxml2/lib' '--enable-thread-safety' '--with-openssl' '--with-gssapi' '--with-bonjour' '--with-libxml' '--with-libxslt' '--with-python' '--with-readline' '--with-uuid=e2fs' 'CFLAGS=-Wno-error=implicit-function-declaration' Alas, there does not seem to be a setting regarding the timezone. Adrian Klaver's comment lead me to find out some more information. Since this thread sort of split, I would continue there. Please chime in in the other thread if you have further suggestions. Kind regards, Adnan Dautovic
Re: Failure of postgres_fdw because of TimeZone setting
Dear Adrian,
Adrian Klaver wrote:
Define 'read-only', especially as it applies to the privileges on the
public schema.
I am not quite sure which information you are looking for
exactly. According to this [1], I ran the following query:
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS
"create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
FROM "names";
And recieved the following result:
"name""create" "usage"
"public" truetrue
Per Tom Lane's comments on timezone, log into the remote server and do:
SHOW timezone;
Europe/Berlin
SET timezone = 'etc/UTC';
ERROR: invalid value for parameter "TimeZone": "etc/UTC"
SQL state: 22023
SET timezone = 'UTC';
ERROR: invalid value for parameter "TimeZone": "UTC"
SQL state: 22023
However, this lead me to [2] and I find the output very
interesting:
SELECT * FROM pg_timezone_names ORDER BY name;
"name""abbrev" "utc_offset" "is_dst"
"Turkey" "+03" "03:00:00"false
"UCT" "UCT" "00:00:00"false
"Universal" "UTC" "00:00:00"false
"W-SU""MSK" "03:00:00"false
And then attempting
SET timezone = 'Universal';
SET
Query returned successfully in 100 msec.
Any ideas on how to proceed?
Kind regards,
Adnan Dautovic
[1]: https://stackoverflow.com/a/36095257
[2]: https://stackoverflow.com/a/32009497
Re: Failure of postgres_fdw because of TimeZone setting
Adnan Dautovic writes: > However, this lead me to [2] and I find the output very > interesting: > SELECT * FROM pg_timezone_names ORDER BY name; >> "name" "abbrev""utc_offset""is_dst" >> "Turkey" "+03" "03:00:00" false >> "UCT""UCT" "00:00:00" false >> "Universal" "UTC" "00:00:00" false >> "W-SU" "MSK" "03:00:00" false Wow. To clarify, is that the *whole* result? But even if you just excerpted it, you're clearly missing a lot of entries. Per your other answer, the remote DB doesn't seem to have been built with --with-system-tzdata, so it must be relying on a Postgres-private copy of the tzdb data set, and evidently a fair number of entries in that have gone missing. Postgres itself would never modify that data after installation, so we're left to speculate about filesystem corruption or somebody's odd desire to remove "unnecessary" files. Out of curiosity, does SET timezone to 'GMT'; work? regards, tom lane
Re: Failure of postgres_fdw because of TimeZone setting
On 4/5/24 02:39, Adnan Dautovic wrote:
Dear Adrian,
Adrian Klaver wrote:
Define 'read-only', especially as it applies to the privileges on the
public schema.
I am not quite sure which information you are looking for
exactly. According to this [1], I ran the following query:
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS
"create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS
"usage"
FROM "names";
And recieved the following result:
"name" "create" "usage"
"public" true true
Looks alright. The below is the issue.
Per Tom Lane's comments on timezone, log into the remote server and do:
SHOW timezone;
Europe/Berlin
SET timezone = 'etc/UTC';
ERROR: invalid value for parameter "TimeZone": "etc/UTC"
SQL state: 22023
SET timezone = 'UTC';
ERROR: invalid value for parameter "TimeZone": "UTC"
SQL state: 22023
However, this lead me to [2] and I find the output very
interesting:
SELECT * FROM pg_timezone_names ORDER BY name;
The below is cut down from the actual output as there should be at least:
Europe/Berlin CEST 02:00:00 t
present also?
"name" "abbrev" "utc_offset" "is_dst"
"Turkey" "+03" "03:00:00" false
"UCT" "UCT" "00:00:00" false
Hmm I get:
UCT UTC 00:00:00 f
could be version difference though.
"Universal" "UTC" "00:00:00" false
"W-SU" "MSK" "03:00:00" false
And then attempting
SET timezone = 'Universal';
SET
Query returned successfully in 100 msec.
Any ideas on how to proceed?
1) For the long term contact whomever is in charge of the remote server
and ask them what they have done with the timezones, why and can they
fix it?
2) In short term per the link from your first post and with no guarantees:
https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677
In the source code change
do_sql_command(conn, "SET timezone = 'UTC'");
to
do_sql_command(conn, "SET timezone = 'Universal'");
As from the link: "Set remote timezone; this is basically just cosmetic"
Then recompile the extension.
Kind regards,
Adnan Dautovic
[1]: https://stackoverflow.com/a/36095257
[2]: https://stackoverflow.com/a/32009497
--
Adrian Klaver
adrian.kla...@aklaver.com
Role Graph for PostgreSQL (v16+) v1-Beta
Hey All, Last year with the revisions to CREATEROLE I decided that having a view that allowed one to more readily see the membership graph of their cluster would be useful. I originally tried to write something up for psql but decided that was impractical given its requirements. Therefore, I put together a pseudo-extension for this. I've finally decided it is time to share it with the world. Since no one but myself has seen it I still consider it beta. At some point I intend to put out an announcement and a bit more formality around its release but was hoping to get some feedback first. So I share it here. For more details please check out the README file. https://github.com/polobo/RoleGraphForPostgreSQL Feedback is much appreciated. Thanks! David J.
