Stack Overflow
Stack Overflow

Questions tagged [pg-dump]

pg_dump is a utility for backing up a PostgreSQL database.

656 questions
10
votes
3 answers

Is it possible to import one database from pg_dumpall

I would like to get a database by name from a pg_dumpall and import it into my local postgres. If possible, I'd like to use a different database name on my local machine. Is this possible?
brock
  • 2,302
  • 7
  • 27
  • 30
10
votes
4 answers

heroku pgbackups:restore: Invalid dump format

I have a local psql database dump that needs to be uploaded to heroku. I followed the steps in the Update section from this link. Everything worked fine until the last part, the actual uploading step: heroku pgbackups:restore --app myAppName…
AndraD
  • 2,830
  • 6
  • 38
  • 48
9
votes
2 answers

pg_dump from view has no data in postgresql 10

pg_dump --table=export_view --data-only --column-inserts mydb > export_view.sql pg_dump (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg18.04+1) Export specific rows from a PostgreSQL table as INSERT SQL script and the postgresql documentation…
AntonOfTheWoods
  • 809
  • 13
  • 17
9
votes
3 answers

PostgreSql : ERROR: relation "sequence" does not exist while restoring from dump file

I get the following error while restoring database from dump file on server: ERROR: relation "table_id_seq" does not exist LINE 1: SELECT pg_catalog.setval('table_id_seq', 362, true); my local psql version is 10.2 server psql version is…
Saly
  • 1,446
  • 3
  • 11
  • 18
9
votes
2 answers

pg_dump, dump options "pre-data", What are the "data definition items" included in "pre_data"?

I am reading the doc of PG Admin 4 about backup dialog box which uses the pg_dump utility. In the dump options tab, there is a sub-menu Section with three switches. One of the switch is named "pre-data" and the doc explains: Move the switch next to…
nyluje
  • 3,573
  • 7
  • 37
  • 67
9
votes
2 answers

Doing pg_dump while still many active transaction

As subjects, what will happen to the backup file while there is still many active transaction in the database. Does it export realtime or just partially backups ? thanks in advance.
Brain90
  • 1,551
  • 18
  • 21
9
votes
1 answer

A lot of "invalid command \N" when I try to restore PostgreSQL dump

I made a backup of a database on my mac and tried to restore it on a computer with ubuntu. When I execute psql -U uname -d dbname -f ~/dump_from_mac I have a lot of error messages like "invalid command \N" and "relation 'SomeTable' does not…
Andrey
  • 320
  • 1
  • 2
  • 11
9
votes
2 answers

pg_dump from remote server to localhost

Hi can anyone help me dump from a postgreSQL database on a remote AWS server to a postgreSQL database on my local machine. I've been trying to do it using the answer in this stack post but it keeps failing. The command I'm using is pg_dump -C -h…
Al D
  • 657
  • 2
  • 10
  • 27
9
votes
1 answer

How can I pg_dump a materialized view as a materialized view and not a table?

I'm attempting to pg_dump (v9.3.4) from one db and pg_restore into another (v9.3.6). I have several materialized views (some with indexes and some with no indexes). One of my materialized views is being dumped as a table (I have confirmed this by…
three-cups
  • 4,375
  • 3
  • 31
  • 41
8
votes
1 answer

What is the purpose of views' predefinitions in dump file

I'm working on the project where we are using :sql schema format for the Active Record dumper (to support more complex logic like triggers). We have a lot of views and I can see in the database/structure.sql file that some of them have "predefined"…
Maciej Małecki
  • 2,725
  • 19
  • 29
8
votes
1 answer

restoring table with pg_restore does not include primary key or indexes

So I made a backup of a table using pg_dump: pg_dump -U bob -F c -d commerce -t orders > orders.dump This table had several listed indexes such as a primary key However when I restore this table into a development database on another system using…
mk2000
  • 81
  • 1
  • 2
8
votes
1 answer

Postgresql - Dump database with x tables - schema only but data from one table

wondering about a little convenience problem of mine atm. Say I got a database with a variable amount of tables and I want a dump of that database BUT only the table structure and the data of one specific table. It is, of course, basically possible…
Samarek
  • 444
  • 1
  • 8
  • 22
8
votes
4 answers

pg_dump on Database throwing error 'out of shared memory'

Getting problem when taking backup on database contains around 50 schema with each schema having around 100 tables. pg_dump throwing below error suggesting that to increase max_locks_per_transaction. pg_dump: WARNING: out of shared memory pg_dump:…
pgollangi
  • 848
  • 3
  • 13
  • 28
8
votes
1 answer

Running pg_dump while insert queries are still running?

If I run pg_dump to dump a table into a SQL file, does it take a snapshot of the last row in the table, and dump all the rows up to this row? Or does it keep dumping all the rows, even those that were inserted after pg_dump was ran? A secondary…
Henley
  • 21,258
  • 32
  • 119
  • 207
8
votes
3 answers

2 versions of pg_dump on my OSX Lion

I had Postgres 9.0.x installed with homebrew, but because of lack of some modules I removed it using: brew uninstall postgres and installed Postgres 9.1.3 from http://www.enterprisedb.com/downloads/postgres-postgresql-downloads. Now I've got a…
Andrey Eremin
  • 287
  • 1
  • 4
  • 14
1 2 3
…
43 44