Questions tagged [pg-upgrade]

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.

Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g. by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade supports upgrades from 8.3.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.

Source: http://www.postgresql.org/docs/9.2/static/pgupgrade.html

42 questions
38
votes
3 answers

"cannot write to log file pg_upgrade_internal.log" when upgrading from Postgresql 9.1 to 9.3

I keep on getting the above error whenever I run the following command via a postgres user. /usr/lib/postgresql/9.3/bin/pg_upgrade \ -b /usr/lib/postgresql/9.1/bin/ \ -B /usr/lib/postgresql/9.3/bin/ \ -d /var/lib/postgresql/9.1/main \ -D…
Constantine M
  • 1,507
  • 2
  • 11
  • 8
10
votes
2 answers

could not open version file PG_VERSION postgresql pg upgrade

I need some help please, I am upgrading postgresql from v10 to v11 on Windows, but get the following error message: could not open version file: C:/Program Files/PostgresSQL/11/data/PG_VERSION. Here are the steps taken: Stopped both PostgresSQL…
Dase M
  • 113
  • 1
  • 7
8
votes
1 answer

pg_upgrade on Windows cannot write to log file pg_upgrade_internal.log

I'm trying to run pg_upgrade on Windows 2008R2, but I'm getting the error: cannot write to log file pg_upgrade_internal.log Failure, exiting I saw a similar question for Linux at 23216734 which explains that the issue is with permissions, but it…
isapir
  • 21,295
  • 13
  • 115
  • 116
6
votes
1 answer

Are there any negative performance or functionality downsides to using pg_upgrade with --link option afterwards?

I'm about upgrade a quite large PostgreSQL cluster from 9.3 to 11. The upgrade The cluster is approximately 1,2Tb in size. The database has a disk system consisting of a fast HW RAID 10 array of 8 DC-edition SSDs with 192GB ram and 64 cores. I am…
Niels Kristian
  • 8,661
  • 11
  • 59
  • 117
3
votes
1 answer

PANIC: could not open control file "global/pg_control": Permission denied

I have to postgres 11 and 14 installed on my computer. When I run pg_upgrade to migrate the databases I get the following error: PANIC: could not open control file "global/pg_control": Permission denied However if I give the Window User full…
Fab C
  • 65
  • 1
  • 6
3
votes
0 answers

redhat pg_upgrade permission errors

duplicate of unanswered question: CentOS 7 pg_upgrade Permissions Errors when trying to run pg_upgrade, it won't let me run the command as root. when exiting from root, it can't access files due to permissions. how can this be solved? command i'm…
Jelly Wu
  • 281
  • 1
  • 3
  • 10
3
votes
2 answers

postgresql with timescale upgrade

I have an installation of Postgresql 9.6.11 with shared library of Timescale 1.1.1 installed. I am trying to upgrade both of them, using in-place upgrade for Timescale and pg_upgrade for Postgres, i.e Postgresql to 11.5 and Timescale to 1.4. Which…
Nitesh Kumar Anand
  • 621
  • 1
  • 6
  • 18
3
votes
1 answer

pg_upgradecluster taking too much time(around 8 hours for 165GB database) any workarounds?

I am trying upgrade postgres-9.3 to postgres-10 with database size around 165GB. I am using "sudo pg_upgradecluster 9.3 main" to do so but it's taking around 8hours which is way too much downtime for my live webapp. Any suggestions to make it better…
3
votes
0 answers

pg_upgradecluster: dump or upgrade

Why does pg_upgradecluster defaults to using dump/restore method instead of using pg_upgrade? The pg_upgrade method should be a lot faster than the dump/restore process. Is the upgrade method not safe? Thanks in advance.
Gonçalo Marrafa
  • 2,013
  • 4
  • 27
  • 34
2
votes
0 answers

pg_upgrade failure while upgrading from v11 to v14

I am using pg_upgrade to move data from v11 to v14, but it is failing because of one database. Before running upgrade I use to check the cluster compatibility, which seems well. below is log. pg_upgrade -d /Users/Pro/pg_backup/postgres.old -D…
Alpha Geek
  • 457
  • 7
  • 17
2
votes
1 answer

Postgresql Upgrade from 11 to 12 fails due to "type abstime does not exist"

I'm running an old postgresql database that was originally installed as v9.6 and I'm progressively upgrading it from one version to the next until I can get it up to date with version 13.3. The upgrades from 9.6 to 10, and 10 to 11 were successful.…
Adam Steinberger
  • 545
  • 1
  • 5
  • 16
2
votes
2 answers

docker - postgres upgrade 11 to 12 postmaster issue

I used this https://github.com/tianon/docker-postgres-upgrade to upgrade postgres from 11 to 12 version & facing issue while upgrade, got error - "postmaster servicing the old cluster" Here using docker container for 11 & 12 in separately, also…
user2128514
  • 47
  • 2
  • 9
2
votes
1 answer

PostgreSQL 10 => 11.1 on Brew Update Issues

Trying to find a migration route from PostgreSQL 10.6 to postgreSQL 11.1. Using pg_upgrade, from both versions, give me errors. From 11.1 which I thought would be used to bring outdated tables to currency: $…
Rich_F
  • 1,830
  • 3
  • 24
  • 45
2
votes
1 answer

CentOS 7 pg_upgrade Permissions Errors

I'm trying to migrate PostgreSQL from 9.2 to 10.2 on CentOS 7 I've installed the new version, and I'm trying to implement the changeover: /usr/pgsql-10/bin/pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/10/data/…
Rich_F
  • 1,830
  • 3
  • 24
  • 45
2
votes
3 answers

Syntax error near "=>" when running pg_upgrade 9.4 to 9.5

I am unable to upgrade my postgresql server from 9.4 to 9.5 due to the following error message: pg_restore: creating OPERATOR "public.->" pg_restore: creating OPERATOR "public.<@" pg_restore: creating OPERATOR "public.=>" pg_restore: [archiver (db)]…
wmakley
  • 1,233
  • 9
  • 18
1
2 3