You are not logged in.

Pages: 1

#1 2020-02-06 12:07:43

SkeleCore
Member
Registered: 2020-02-06
Posts: 2

postgresql pg_upgrade different major versions

I updated PostgreSQL on a Arch Linux server to 12.1. I am having issues when trying to migrate my data to the new version.

sudo systemctl status postgresql.service
[sudo] password for chris: 
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Fri 2020-01-31 05:49:47 EST; 6 days ago

Jan 31 05:49:47 vps187772 systemd[1]: Starting PostgreSQL database server...
Jan 31 05:49:47 vps187772 postgres[396]: An old version of the database format was found.
Jan 31 05:49:47 vps187772 postgres[396]: See https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL
Jan 31 05:49:47 vps187772 systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Jan 31 05:49:47 vps187772 systemd[1]: postgresql.service: Failed with result 'exit-code'.
Jan 31 05:49:47 vps187772 systemd[1]: Failed to start PostgreSQL database server.
psql --version
psql (PostgreSQL) 12.1

I then followed the steps at https://wiki.archlinux.org/index.php/Po … PostgreSQL.
I tried to use pg_upgrade, however the only version available in /opt for pgsql is pgsql-11.

pg_upgrade -b /opt/pgsql-11/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   
Old cluster data and binary directories are from different major versions.
Failure, exiting
cat /var/lib/postgres/data/PG_VERSION
12
cat /var/lib/postgres/olddata/PG_VERSION
10

I looked at the older versions and the next one is postgresql-96-upgrade.
I installed that which replaced postgres-old-upgrade and then tried to run pg_upgrade with that instead.

pg_upgrade -b /opt/pgsql-9.6/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   
Old cluster data and binary directories are from different major versions.
Failure, exiting

I am not sure what I should try next. Is there a reason why there is no package for version 10 or did I make a mistake in the migration process.

Also it is my first time posting here. Let me know if you need any other details, logs etc.

Last edited by SkeleCore (2020-02-06 12:08:16)

Whats your favorite thing about space? Mine is space.

Offline

#2 2020-02-06 15:38:23

eda2z
Member
From: Woodstock, IL
Registered: 2015-04-21
Posts: 71

Re: postgresql pg_upgrade different major versions

I have never tried to skip a version when I have upgraded. You may have to go from 10 to 11 and then 11 to 12. There are postgresql-old-upgrade packages for version 10 in the Arch Archives (archive.archlinux.org).

Offline

#3 2020-02-06 16:53:30

SkeleCore
Member
Registered: 2020-02-06
Posts: 2

Re: postgresql pg_upgrade different major versions

Thanks for the suggestion. I had a look at the archives for postgres-old-upgrade at https://archive.archlinux.org/packages/ … d-upgrade/
Tried postgresql-old-upgrade-10.10-1-x86_64.pkg.tar.xz first but there was a issue with a missing library. Next I tried postgresql-old-upgrade-10.10-3-x86_64.pkg.tar.xz and that worked with pg_upgrade.

This is the command I used to install.

sudo pacman -U https://archive.archlinux.org/packages/p/postgresql-old-upgrade/postgresql-old-upgrade-10.10-3-x86_64.pkg.tar.xz

Then I restarted PostgreSQL service and it status was okay. Although when I checked the database it seems my data was gone.
I am not sure if I backed it up correctly. I was not storing much on it as it was a simple login database but still a pain to recreate.

I then tried the manual dump to /tmp/old_backup.sql to see if that contained it and it doesn't.

[postgres@vps187772 ~]$ cat /tmp/old_backup.sql 
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE chris;
ALTER ROLE chris WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE minijack;
ALTER ROLE minijack WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE rw;
ALTER ROLE rw WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;






--
-- Databases
--

--
-- Database "template1" dump
--

\connect template1

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.10
-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

--
-- Database "postgres" dump
--

\connect postgres

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.10
-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

--
-- Database "test" dump
--

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.10
-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: test; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


ALTER DATABASE test OWNER TO postgres;

\connect test

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

--
-- Database "testdb" dump
--

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.10
-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: testdb; Type: DATABASE; Schema: -; Owner: chris
--

CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


ALTER DATABASE testdb OWNER TO chris;

\connect testdb

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database cluster dump complete
--

I might have messed something up without realizing. Hard to tell. If it is gone then I will go and recreate it instead and hopefully avoid a similar issue
in the future.

Whats your favorite thing about space? Mine is space.

Offline

#4 2020-02-06 22:00:50

loqs
Member
Registered: 2014-03-06
Posts: 19,089

Re: postgresql pg_upgrade different major versions

You could try downgrading the system to a data where postgresql 10 was the current version using the ALA and try recovering the data from /var/lib/postgres/olddata

Offline

Pages: 1

Board footer

Powered by FluxBB