Postgres Pro
Downloads
Home   >   mailing lists

Thread: restore question

restore question

From
Ronald Haynes
Date:
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
);

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.

Re: restore question

From
Ray O'Donnell
Date:
On 19/07/2022 11:11, Ronald Haynes wrote:
> 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.
> 

That looks like a dump file generated by pg_dumpall, so restoring it 
should be as simple as:

    psql -f backup-file.sql

You'll possibly need other connection parameters as required by psql, 
such as -U (the PostgreSQL user to use for connecting) or others.

HTH,

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: restore question

From
Ronald Haynes
Date:
Thanks Ray, running 

pSql -f backup-file.sql 



psql: error: FATAL: database "rhaynes74" does not exist

Which seems odd since rhaynes74 is a user not a database name in the file. 

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.
On Jul 19, 2022, 9:40 AM -0230, Ray O'Donnell <ray@rodonnell.ie>, wrote:
On 19/07/2022 11:11, Ronald Haynes wrote:
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.


That looks like a dump file generated by pg_dumpall, so restoring it
should be as simple as:

psql -f backup-file.sql

You'll possibly need other connection parameters as required by psql,
such as -U (the PostgreSQL user to use for connecting) or others.

HTH,

Ray.


--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: restore question

From
Benedict Holland
Date:
You have to create the database to restore to. Then pg_resrore should work.

Thanks,
Ben

On Tue, Jul 19, 2022, 12:44 PM Ronald Haynes <rhaynes74@gmail.com> wrote:
Thanks Ray, running 

pSql -f backup-file.sql 



psql: error: FATAL: database "rhaynes74" does not exist

Which seems odd since rhaynes74 is a user not a database name in the file. 

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.
On Jul 19, 2022, 9:40 AM -0230, Ray O'Donnell <ray@rodonnell.ie>, wrote:
On 19/07/2022 11:11, Ronald Haynes wrote:
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.


That looks like a dump file generated by pg_dumpall, so restoring it
should be as simple as:

psql -f backup-file.sql

You'll possibly need other connection parameters as required by psql,
such as -U (the PostgreSQL user to use for connecting) or others.

HTH,

Ray.


--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: restore question

From
Adrian Klaver
Date:
On 7/19/22 9:49 AM, Benedict Holland wrote:
> You have to create the database to restore to. Then pg_resrore should work.

The OP is not using pg_restore but psql as the dump file is plain text.
> 
> Thanks,
> Ben

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: restore question

From
Adrian Klaver
Date:
On 7/19/22 9:44 AM, Ronald Haynes wrote:
> Thanks Ray, running
> 
> pSql -f backup-file.sql
> 
> 
> 
> psql: error: FATAL: database "rhaynes74" does not exist

No that is expected as you did not specify a database to connect to 
using -d <some_db>. In that case psql uses the OS user name you are 
running the command as for the database name per:

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

dbname

     The database name. Defaults to be the same as the user name. In 
certain contexts, the value is checked for extended formats; see Section 
34.1.1 for more details on those.

So change the command to:

psql -d postgres  -U <db_user> -f backup-file.sql

Where <db_user> is a database user that has the correct privileges to 
load/create the databases.

> 
> Which seems odd since rhaynes74 is a user not a database name in the file.
> 
> Sincerely,
> 
> Dr. Ronald D. Haynes
> Professor, Department of Mathematics and Statistics
> Chair, MSc and Phd Scientific Computing Programs
> Memorial University of Newfoundland



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: restore question

From
Ronald Haynes
Date:
Many thanks folks, the last message by Adrian gave me the relatively simple prescription. 

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.
On Jul 19, 2022, 2:22 PM -0230, Adrian Klaver <adrian.klaver@aklaver.com>, wrote:
On 7/19/22 9:44 AM, Ronald Haynes wrote:
Thanks Ray, running

pSql -f backup-file.sql



psql: error: FATAL: database "rhaynes74" does not exist

No that is expected as you did not specify a database to connect to
using -d <some_db>. In that case psql uses the OS user name you are
running the command as for the database name per:

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

dbname

The database name. Defaults to be the same as the user name. In
certain contexts, the value is checked for extended formats; see Section
34.1.1 for more details on those.

So change the command to:

psql -d postgres -U <db_user> -f backup-file.sql

Where <db_user> is a database user that has the correct privileges to
load/create the databases.


Which seems odd since rhaynes74 is a user not a database name in the file.

Sincerely,

Dr. Ronald D. Haynes
Professor, Department of Mathematics and Statistics
Chair, MSc and Phd Scientific Computing Programs
Memorial University of Newfoundland



--
Adrian Klaver
adrian.klaver@aklaver.com