PostgresSQL dump loading succeed but nothing is written on the database

I've try to load a dump to a new database and all seems to work :

user@vpsXXXX:~$ pg_dump -U user -d database < mydump.sql

--

-- PostgreSQL database dump

--



-- Dumped from database version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

-- Dumped by pg_dump version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.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 client_min_messages = warning;

SET row_security = off;



--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--



CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;





--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

--



COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';





--

-- PostgreSQL database dump complete

--

When I look the tables on a software like Postico, there is no tables except the Postgres ones. My dump is complete when I look the SQL file.

Do you know a tip to know what happens ?

Thanks !

asked Nov 15 '18 at 21:52

Zoloom

2422611

1

the command you are executing looks weird, you are passing a file to pg_dump which is supposed to dump a database into a file, not load it. the arrow should be the other way around (> instead of <, meaning dump the standard output of the comand to filename mydump.sql). also in what I suppose is the output of the command, I see no table definitions? are you sure this is the right database?

– Uku Loskit
Nov 15 '18 at 22:20

Yes you're right, thats the wrong command. So I use psql -U user database < dump.sql and it worked. But I've got some strange errors like : CREATE EXTENSION ERROR: must be owner of extension plpgsql or WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT Have you ever see this ? Thanks for your help !

– Zoloom
Nov 15 '18 at 22:25

1

you should do the dumps/restores as postgres users, otherwise you will have to do a lot of permissions mangling.

– Uku Loskit
Nov 15 '18 at 22:31

1

yes, that's expected. you either run dump/psql as the postgres user (sudo su - postgres`) or add password to postgres user (not recommended)

– Uku Loskit
Nov 15 '18 at 22:40

1

if the new database does not have the same roles, easiest way to resolve this is to change the ownership of tables to your user e.g alter table <name> owner to <user>

– Uku Loskit
Nov 15 '18 at 22:53

|
show 10 more comments

I've try to load a dump to a new database and all seems to work :

user@vpsXXXX:~$ pg_dump -U user -d database < mydump.sql

--

-- PostgreSQL database dump

--



-- Dumped from database version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

-- Dumped by pg_dump version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.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 client_min_messages = warning;

SET row_security = off;



--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--



CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;





--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

--



COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';





--

-- PostgreSQL database dump complete

--

When I look the tables on a software like Postico, there is no tables except the Postgres ones. My dump is complete when I look the SQL file.

Do you know a tip to know what happens ?

Thanks !

asked Nov 15 '18 at 21:52

Zoloom

2422611

1

the command you are executing looks weird, you are passing a file to pg_dump which is supposed to dump a database into a file, not load it. the arrow should be the other way around (> instead of <, meaning dump the standard output of the comand to filename mydump.sql). also in what I suppose is the output of the command, I see no table definitions? are you sure this is the right database?

– Uku Loskit
Nov 15 '18 at 22:20

Yes you're right, thats the wrong command. So I use psql -U user database < dump.sql and it worked. But I've got some strange errors like : CREATE EXTENSION ERROR: must be owner of extension plpgsql or WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT Have you ever see this ? Thanks for your help !

– Zoloom
Nov 15 '18 at 22:25

1

you should do the dumps/restores as postgres users, otherwise you will have to do a lot of permissions mangling.

– Uku Loskit
Nov 15 '18 at 22:31

1

yes, that's expected. you either run dump/psql as the postgres user (sudo su - postgres`) or add password to postgres user (not recommended)

– Uku Loskit
Nov 15 '18 at 22:40

1

if the new database does not have the same roles, easiest way to resolve this is to change the ownership of tables to your user e.g alter table <name> owner to <user>

– Uku Loskit
Nov 15 '18 at 22:53

|
show 10 more comments

I've try to load a dump to a new database and all seems to work :

user@vpsXXXX:~$ pg_dump -U user -d database < mydump.sql

--

-- PostgreSQL database dump

--



-- Dumped from database version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

-- Dumped by pg_dump version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.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 client_min_messages = warning;

SET row_security = off;



--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--



CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;





--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

--



COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';





--

-- PostgreSQL database dump complete

--

When I look the tables on a software like Postico, there is no tables except the Postgres ones. My dump is complete when I look the SQL file.

Do you know a tip to know what happens ?

Thanks !

asked Nov 15 '18 at 21:52

Zoloom

2422611

I've try to load a dump to a new database and all seems to work :

user@vpsXXXX:~$ pg_dump -U user -d database < mydump.sql

--

-- PostgreSQL database dump

--



-- Dumped from database version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

-- Dumped by pg_dump version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.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 client_min_messages = warning;

SET row_security = off;



--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--



CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;





--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

--



COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';





--

-- PostgreSQL database dump complete

--

When I look the tables on a software like Postico, there is no tables except the Postgres ones. My dump is complete when I look the SQL file.

Do you know a tip to know what happens ?

Thanks !

postgresql ubuntu-18.04

asked Nov 15 '18 at 21:52

Zoloom

2422611

asked Nov 15 '18 at 21:52

Zoloom

2422611

asked Nov 15 '18 at 21:52

Zoloom

2422611

asked Nov 15 '18 at 21:52

Zoloom

2422611

asked Nov 15 '18 at 21:52

Zoloom

2422611

1

the command you are executing looks weird, you are passing a file to pg_dump which is supposed to dump a database into a file, not load it. the arrow should be the other way around (> instead of <, meaning dump the standard output of the comand to filename mydump.sql). also in what I suppose is the output of the command, I see no table definitions? are you sure this is the right database?

– Uku Loskit
Nov 15 '18 at 22:20

Yes you're right, thats the wrong command. So I use psql -U user database < dump.sql and it worked. But I've got some strange errors like : CREATE EXTENSION ERROR: must be owner of extension plpgsql or WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT Have you ever see this ? Thanks for your help !

– Zoloom
Nov 15 '18 at 22:25

1

you should do the dumps/restores as postgres users, otherwise you will have to do a lot of permissions mangling.

– Uku Loskit
Nov 15 '18 at 22:31

1

yes, that's expected. you either run dump/psql as the postgres user (sudo su - postgres`) or add password to postgres user (not recommended)

– Uku Loskit
Nov 15 '18 at 22:40

1

if the new database does not have the same roles, easiest way to resolve this is to change the ownership of tables to your user e.g alter table <name> owner to <user>

– Uku Loskit
Nov 15 '18 at 22:53

|
show 10 more comments

1

the command you are executing looks weird, you are passing a file to pg_dump which is supposed to dump a database into a file, not load it. the arrow should be the other way around (> instead of <, meaning dump the standard output of the comand to filename mydump.sql). also in what I suppose is the output of the command, I see no table definitions? are you sure this is the right database?

– Uku Loskit
Nov 15 '18 at 22:20

Yes you're right, thats the wrong command. So I use psql -U user database < dump.sql and it worked. But I've got some strange errors like : CREATE EXTENSION ERROR: must be owner of extension plpgsql or WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT Have you ever see this ? Thanks for your help !

– Zoloom
Nov 15 '18 at 22:25

1

you should do the dumps/restores as postgres users, otherwise you will have to do a lot of permissions mangling.

– Uku Loskit
Nov 15 '18 at 22:31

1

yes, that's expected. you either run dump/psql as the postgres user (sudo su - postgres`) or add password to postgres user (not recommended)

– Uku Loskit
Nov 15 '18 at 22:40

1

if the new database does not have the same roles, easiest way to resolve this is to change the ownership of tables to your user e.g alter table <name> owner to <user>

– Uku Loskit
Nov 15 '18 at 22:53

the command you are executing looks weird, you are passing a file to pg_dump which is supposed to dump a database into a file, not load it. the arrow should be the other way around (> instead of <, meaning dump the standard output of the comand to filename mydump.sql). also in what I suppose is the output of the command, I see no table definitions? are you sure this is the right database?

– Uku Loskit
Nov 15 '18 at 22:20

Yes you're right, thats the wrong command. So I use psql -U user database < dump.sql and it worked. But I've got some strange errors like : CREATE EXTENSION ERROR: must be owner of extension plpgsql or WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT Have you ever see this ? Thanks for your help !

– Zoloom
Nov 15 '18 at 22:25

you should do the dumps/restores as postgres users, otherwise you will have to do a lot of permissions mangling.

– Uku Loskit
Nov 15 '18 at 22:31

yes, that's expected. you either run dump/psql as the postgres user (sudo su - postgres`) or add password to postgres user (not recommended)

– Uku Loskit
Nov 15 '18 at 22:40

if the new database does not have the same roles, easiest way to resolve this is to change the ownership of tables to your user e.g alter table <name> owner to <user>

– Uku Loskit
Nov 15 '18 at 22:53

|
show 10 more comments

1 Answer
1

active

oldest

votes

Dump and restore operations are best performed as postgres user. The easiest way to achieve this is to become the postgres UNIX user.

The initial command had the mistake of confusing pg_dump with psql.

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53328400%2fpostgressql-dump-loading-succeed-but-nothing-is-written-on-the-database%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Dump and restore operations are best performed as postgres user. The easiest way to achieve this is to become the postgres UNIX user.

The initial command had the mistake of confusing pg_dump with psql.

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

add a comment |

Dump and restore operations are best performed as postgres user. The easiest way to achieve this is to become the postgres UNIX user.

The initial command had the mistake of confusing pg_dump with psql.

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

add a comment |

Dump and restore operations are best performed as postgres user. The easiest way to achieve this is to become the postgres UNIX user.

The initial command had the mistake of confusing pg_dump with psql.

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

Dump and restore operations are best performed as postgres user. The easiest way to achieve this is to become the postgres UNIX user.

The initial command had the mistake of confusing pg_dump with psql.

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

answered Nov 16 '18 at 0:43

Uku Loskit

30.9k86981

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky