Hi folks, I am a fairly novice postgresql user.
I have a backup file for a couple of small postgresql databases. The backup file was created using postgresql 12.2, my Mac now has a postgresql 13.x or 14.x versions. I would like to restore the setup from the backup file.
Psql Postgres followed by \l gives:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+---------+-------+-------------------------
postgres | rhaynes74 | UTF8 | C | C |
template0 | rhaynes74 | UTF8 | C | C | =c/rhaynes74 +
| | | | | rhaynes74=CTc/rhaynes74
template1 | rhaynes74 | UTF8 | C | C | =c/rhaynes74 +
| | | | | rhaynes74=CTc/rhaynes74
The user that owns the databases in the backup do not currently exist.
Here is the header information in the backup file, what are the steps to proceed with the restore?
-----
--
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE rhaynes;
ALTER ROLE rhaynes WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
--
-- Databases
--
--
-- Database "template1" dump
--
\connect template1
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.2
-- Dumped by pg_dump version 12.2
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 "hockey1" dump
--
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.2
-- Dumped by pg_dump version 12.2
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
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: hockey1; Type: DATABASE; Schema: -; Owner: rhaynes
--
CREATE DATABASE hockey1 WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII' LC_COLLATE = 'C' LC_CTYPE = 'C';
ALTER DATABASE hockey1 OWNER TO rhaynes;
\connect hockey1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
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;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: tradelistnew; Type: TABLE; Schema: public; Owner: rhaynes
--
CREATE TABLE public.tradelistnew (
year character varying(80),
manufacturer character varying(80),
set character varying(80),
card_number character varying(5),
player_name character varying(80),
quantitynrmt integer,
quantityexmt integer,
quantityex integer,
quantityvg integer,
quantitypfg integer,
valuenrmt real,
valueexmt real,
valueex real,
valuevg real,
valuepfg real
);
I have a backup file for a couple of small postgresql databases. The backup file was created using postgresql 12.2, my Mac now has a postgresql 13.x or 14.x versions. I would like to restore the setup from the backup file.
Psql Postgres followed by \l gives:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+---------+-------+-------------------------
postgres | rhaynes74 | UTF8 | C | C |
template0 | rhaynes74 | UTF8 | C | C | =c/rhaynes74 +
| | | | | rhaynes74=CTc/rhaynes74
template1 | rhaynes74 | UTF8 | C | C | =c/rhaynes74 +
| | | | | rhaynes74=CTc/rhaynes74
The user that owns the databases in the backup do not currently exist.
Here is the header information in the backup file, what are the steps to proceed with the restore?
-----
--
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE rhaynes;
ALTER ROLE rhaynes WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
--
-- Databases
--
--
-- Database "template1" dump
--
\connect template1
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.2
-- Dumped by pg_dump version 12.2
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 "hockey1" dump
--
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.2
-- Dumped by pg_dump version 12.2
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
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: hockey1; Type: DATABASE; Schema: -; Owner: rhaynes
--
CREATE DATABASE hockey1 WITH TEMPLATE = template0 ENCODING = 'SQL_ASCII' LC_COLLATE = 'C' LC_CTYPE = 'C';
ALTER DATABASE hockey1 OWNER TO rhaynes;
\connect hockey1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
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;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: tradelistnew; Type: TABLE; Schema: public; Owner: rhaynes
--
CREATE TABLE public.tradelistnew (
year character varying(80),
manufacturer character varying(80),
set character varying(80),
card_number character varying(5),
player_name character varying(80),
quantitynrmt integer,
quantityexmt integer,
quantityex integer,
quantityvg integer,
quantitypfg integer,
valuenrmt real,
valueexmt real,
valueex real,
valuevg real,
valuepfg real
);
Sincerely,
Dr. Ronald D. Haynes
Professor, Department of Mathematics and Statistics
Chair, MSc and Phd Scientific Computing Programs
Memorial University of Newfoundland
We acknowledge that the lands on which Memorial University’s campuses are situated are in the traditional territories of diverse Indigenous groups, and we acknowledge with respect the diverse histories and cultures of the Beothuk, Mi’kmaq, Innu, and Inuit of this province.
