pg_dump and restore - pg_restore hangs on Windows

Question

I have around 50gb of data in a postgres database on my laptop (mac) that I need to transfer to my new pc (windows). I've generated tar dumps using pg_dump of the schemas I need to transfer, but pg_restore just hangs.

To eliminate problems with the size of the file and the fact that the source is mac, I've boiled it down to the simplest test case I can find which is to create a new table in a new schema on my PC, export it using pg dump and then try to restore it back into the same database. Even with something this simple, pg_restore just hangs. I'm clearly missing something - probably quite obvious. Any ideas?

D:\Share\dbexport>psql -U postgres
Password for user postgres:
psql (12.1)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# create schema new_schema
postgres-# create table new_schema.new_table(id numeric);
CREATE SCHEMA
postgres=# insert into new_schema.new_table values(1);
INSERT 0 1
postgres=# commit;
WARNING:  there is no transaction in progress
COMMIT
postgres=# exit

Schema is created with new table and 1 row. So export

D:\Share\dbexport>pg_dump -U postgres -n new_schema -f new_schema_sql.sql
Password:

D:\Share\dbexport>more new_schema_sql.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1
-- 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: new_schema; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA new_schema;


ALTER SCHEMA new_schema OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: new_table; Type: TABLE; Schema: new_schema; Owner: postgres
--

CREATE TABLE new_schema.new_table (
    id numeric
);


ALTER TABLE new_schema.new_table OWNER TO postgres;

--
-- Data for Name: new_table; Type: TABLE DATA; Schema: new_schema; Owner: postgres
--

COPY new_schema.new_table (id) FROM stdin;
1
\.

So file has been created and has the expected content. I connect back to the database and drop the new schema before attempting the restore.

--
-- PostgreSQL database dump complete
--

D:\Share\dbexport>psql -U postgres
Password for user postgres:
psql (12.1)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# drop schema new_schema cascade;
NOTICE:  drop cascades to table new_schema.new_table
DROP SCHEMA
postgres=# select * from new_schema.new_table;
ERROR:  relation "new_schema.new_table" does not exist
LINE 1: select * from new_schema.new_table;
                      ^
postgres=# exit

D:\Share\dbexport>pg_restore -U postgres -f new_schema_sql.sql

And it just hangs at this last line. I'm a bit lost - I can't get pg_restore to output anything - I've tried with Verbose mode etc but nothing.

Does anyone know where I should be looking next?

David

You created a SQL script, you need to run that script using psql, pg_restore is only for the custom and tar formats, not for the "text" format,. — a_horse_with_no_name
Thank you for the comment, unfortunately though if I rerun the above exercise using the custom or tar formats, I get the same issue. I'll add an update to the question showing the behaviour — user7863288
The -f parameter of pg_restore specifies an output file (log file), the input file is specified without any parameter switch: postgresql.org/docs/current/app-pgrestore.html — a_horse_with_no_name
Again though, as I mention in the question, I have a large volume of data to transfer that will not work with a simple sql script. My feeling is that there is a problem with pg_restore because if I repeat the exercise with a custom format, it still hangs. — user7863288

user7863288 user7863288 · Accepted Answer · 2020-01-11T12:32:17

So I will buy myself a dunce hat.

The issue as pointed out by @a_horse_with_no_name is that I misused the -f flag. That specifies the output file rather than the input file.

Using

pg_restore -U postgres -d postgres -n new_schema new_schema_custom.sql

fixed the issue. Thank you

pg_dump and restore - pg_restore hangs on Windows

1 Answers