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
0
votes
2 answers

postgres 14 "create publication" stuck for hours

I upgraded from Postgres 10 to Postgres 14 using pg_upgrade --link option. The total databases size is around 10TB. The pg_upgrade was successful and quick and like the tool suggested - Optimizer statistics are not transferred by pg_upgrade. Once…
nullptr
  • 87
  • 1
  • 9
0
votes
1 answer

Postgres pg_upgrade 13.2 to 14, returns out of shared memory. - Amazon Linux release 2 (Karoo)

When attempting to upgrade a Postgres 13.2 database to 14, I receive the error "out of shared memory" when running the upgrade. I have adjusted max_locks_per_transaction to different and really high values, and max_connections and other memory…
Alan
  • 3
  • 2
0
votes
1 answer

is it possible to upgrade postgresql from 9.4 to 13.3 using pg_upgrade?

When i try to Upgrade 9.4 to 13 using pg_upgrade , the Upgrade stops in between with authentication failure using the password from pgpass.conf file . The same approach works from 9.4 to 9.6 Here is the pg_upgrade command that i…
0
votes
0 answers

Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

I am currently upgrading a Postgres cluster running on a Centos machine from version 9.6.22 to 12.7. Post the install of 12.7 binaries, I am running the pg_upgrade command: /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-12/bin/ -d…
0
votes
1 answer

Upgrade postgres 10.6 a 12.3

I upgraded a postgres cluster from version 10.6 to 12.3 with the pg_upgrade command, it was fine, but when I connect with psql they appear: > psql psql (12.3, server 10.6) # select version (); …
Hugo B.
  • 11
  • 2
0
votes
1 answer

How to add environment variable from java code

Want to add "PGPASSWORD" env variable from my java application. I know using below way, we can add. private void setPgPasswordEnv(String pwdValue) { ProcessBuilder pb = new ProcessBuilder("CMD", "/C", "SET"); Map envMap =…
0
votes
1 answer

Postgres - pg_upgrade 11 to 12 role "postgres" does not exist

When trying to upgrade from 11 to 12 by using https://github.com/tianon/docker-postgres-upgrade - the following error pops up: docker run --rm \ -e PGUSER=$POSTGRES_USER \ -e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER" \ -v…
user2128514
  • 47
  • 2
  • 9
0
votes
1 answer

migrating remote postgresql 9.1 database to local postgresql 11

I want to upgrade the postgresql 9.1 database to postgresql 11. First I want to get the data on the remote pg9.1 server with pg_dump from the pg11 server to make an experiment. How do I copy the database on the remote server to the new server with…
0
votes
0 answers

pg_upgrade with brew mac os 9.6.2 - 10.5

I've followed the excellent guide here to upgrade from 9.6.2 to 10.5 on mac os 10.11.6. When I get to the step 6 pg_upgrade -b /usr/local/Cellar/postgresql/9.6.2/bin/ \ -B /usr/local/Cellar/postgresql/10.5/bin/ \ -d /usr/local/Cellar/postgres.old/…
mapping dom
  • 1,737
  • 4
  • 27
  • 50
0
votes
1 answer

pg_upgrade oldbindir check fails

I'm trying to upgrade postgresql database from 9.1 to 9.4. I ran the 9.4 version of pg_upgrade as follows: sudo -u postgres pg_upgrade -b /postgres/9.1/bin -B /postgres/9.4/bin -d /9.1DBs/ -D / I get: check…
ventsyv
  • 3,316
  • 3
  • 27
  • 49
0
votes
0 answers

pg_upgrade on AWS EC2 linux - pg_hba.conf settings

I am running an Amazon EC2 CentOS 6.6 server instance with pre-installed PostgreSQL 8.4.20 server which I want to upgrade to 9.4.1 using pg_upgrade via SSH. What I've done so far: Downloaded and installed PostgreSQL 9.4.1 with yum, configured it.…
the_web
  • 406
  • 1
  • 4
  • 11
-1
votes
1 answer

Error using pg_upgrade going from PostgreSQL 10 to 12

I have an installation of PostgreSQL 10, and just installed PostgreSQL 12 (on Windows). I'm trying to migrate over the databases using pg_upgrade. However, I'm getting an annoying error: The command I ran was as follows: $ pg_upgrade.exe -b…
robbieperry22
  • 1,753
  • 1
  • 18
  • 49
1 2
3