The tag has no usage guidance.

learn more… | top users | synonyms

2
votes
1answer
30 views

pg_dump - schema not found

I am trying to dump a schema from Postgresql 9.4, running on Cents 6.5, but am getting an error "pg_dump: No matching schemas were found" Here is the command I am running: pg_dump -U postgres -n ...
1
vote
1answer
42 views

pg_restore: [archiver] did not find magic string in file header

I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a ...
1
vote
0answers
22 views

pgAdmin export line overflow on large field

Background: I have a database of significant geographical locations downloaded from GeoNames open database. The create script for the table is as follows CREATE TABLE populargeonameswiki ( geonameid ...
0
votes
1answer
22 views

error recovering pg_dump compressed file

I produced this backup: pg_dump -v -Fc -Z5 -f thebackup.dmp.gz thedb I can't open the dump with gzip, 7zip... I could recover the same files in the past without using -Fc parameter. Is it possible ...
2
votes
0answers
29 views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
1
vote
0answers
60 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query ...
1
vote
1answer
71 views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
3
votes
1answer
84 views

Local Backup from Amazon RDS PostgreSQL database

I have a PostgreSQL database on an Amazon RDS instance. I want to have a local dump of this instance. So far, I've tried with this: PGPASSWORD="Password" pg_dump -h ...
0
votes
0answers
58 views

Why postgresql pg_dump exports a view as a table?

I am using postgresql 9.3 pg_dump tool for extracting only the public schema definition using: pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql but when I check schema.sql one ...
1
vote
0answers
120 views

postgresql pg_dump no matching schemas [closed]

I have a local postgres 9.4 installation with pg_dump. I am trying to do a remote pg_dump where I'm looking to dump only the schema DDL with NO data. I am getting the error: pg_dump: No matching ...
1
vote
1answer
34 views

pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4). well, here's the command: pg_dump -f file.sql -F plain --inserts -n schema database Ok. My ...
3
votes
0answers
58 views

Restoring Postgres fixtures into a different database with different premissions

I've got an application called BigSense that uses two database users/rolls. One has full permissions and is used by my application to create/adjust table definitions. Then the application starts with ...
1
vote
1answer
166 views

PostgreSQL - partial dump of large orbjects (pg_largeobject table)

Context : Our web-app is like a collaboration platform where you can share files, appointments, survey and so on. An instance of this web-app is divided in communities, each one is independent to each ...
3
votes
1answer
238 views

Is it possible to backup and restore a materialized view with it's data?

I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing. Is it possible in PostgreSQL 9.4? (the alternative ...
1
vote
1answer
46 views

How can I share pg_dump output with my team so they can restore without errors?

I am on a team that is building a Drupal website. All of the settings, including the migration scripts from the old site, will be in code, so in theory all of our work can be replicated just from a ...
1
vote
1answer
267 views

Postgres Restore Not Restoring Bytea Data

This is a very urgent situation. We have a postgres 9.4 database installed on a CentOs machine. We are trying to take a backup from a Heroku Postgres database and restore it to the database on ...
0
votes
0answers
44 views

How to do a pgdump with strings encoded?

When I perform a dump with pg_dump tool, with a "flat" sql format, pg_dump create a regular sql file but the restoration with pg_restore fails because the strings are not escaped. For example, single ...
1
vote
1answer
1k views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
0
votes
2answers
296 views

Using pg_dump and psql to transfer table with timestamp into another Db

I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another. pg_dump -U postgres -t public.table1 database1 | psql -U ...
1
vote
1answer
58 views

How can prevent writes to a Postgres schema for a period of time?

I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during ...
1
vote
1answer
96 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...
0
votes
1answer
80 views

pg_get_viewdef error while running pg_dump in PostgreSQL

Occasionally, I get the following error while doing a simple pg_dump ( pg_dump -U postgres -Fc -f mybackup.dmp mydb): pg_dump: [archiver (db)] query failed: ERROR: could not open relation with OID ...
1
vote
2answers
513 views

How to restore postgreSQL schema dump to schema with different name

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema. The dump type is "custom". So effectively I want ...
1
vote
1answer
1k views

Can I use pg_restore to restore data to subset of tables?

I am trying to restore data in to select tables of a database (9.3.5). This is a data refresh from production to test, while maintaining some table data in the test database (like application users ...
5
votes
2answers
6k views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
0
votes
1answer
422 views

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases: /usr/lib/postgresql/9.3/bin/pg_dump --schema-only -h stg-db.....rds.amazonaws.com ...
1
vote
0answers
80 views

Importing PostgreSQL backup into slightly different database

I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to ...
0
votes
1answer
107 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
0
votes
1answer
1k views

Postgres 9.3 changes the standard_conforming_strings value

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3 In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default ...
3
votes
1answer
667 views

In Postgresql, after pg_dump the access privileges on the database itself are not restored

Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee gee=> \l List of databases Name | Owner | ...
0
votes
1answer
57 views

Postgresql database backup (and restore) from two servers to third storage place

I have two server machines with PostgreSQL: (A) is version 9.0.15 and (B) version 9.2. I wish to make backups of the databases stored in (A) and (B) - and be able to do restore if needed too! - to a ...
0
votes
1answer
543 views

How can I pg_dump my database while excluding extensions?

I am trying to migrate my PostgreSQL 9.3 database to heroku. My database uses the UUID-ossp contrib extension I am not able to load my db onto Heroku because ERROR: permission denied for language ...
3
votes
1answer
47 views

What is the “last” data that get's backed up, when running pg_dump on a live server?

I'm wondering: If I start doing a pg_dump of a very large database (it would take hours), that is still running actively receiving writes, what is then the last data that goes into the backup? Is it: ...
2
votes
2answers
3k views

pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace

I am going to move a database from the old server to a new one. the old one runs PostgreSQL 9.1, the new one has 9.3. I want to change the database name. I want the new database to be in a dedicated ...
0
votes
1answer
67 views

pg_dump inserting C source code in dump file

Am backing up daily my DB with pg_dump. The strange issue is that when I try to restore it with psql, errors are thrown. After opening the dump file with vim and analyzing the line that is causing the ...
0
votes
0answers
316 views

How to copy PostgresSQL database from remote server to LOCAL PC WITHOUT PG_DUMP(ALL)?

How to copy PostgresSQL database from remote server to local PC without pg_dump (or pg_dumpall ? Any format is good (plain text,sql,csv..) I have NO root access to remote PostgresSql database and I ...
2
votes
0answers
242 views

How do you stop Postgres from performing writes inside base/ directory during pg_dump?

We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec ...
4
votes
1answer
3k views

Postgres Dump To TXT File

Is there a way to convert a Postgres dump file to a text file? I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text. ...
1
vote
1answer
2k views

What to look for in bad pg_dump log

We want to programmatically detect errors in cron scheduled pg_dumps. Apart from cheking whether or not the log file ends in "pg_dump: saving database definition": What other tell-tale string can I ...
3
votes
1answer
506 views

pg_dump freezes on windows service application

I have a system running over Tomcat which executes a backup batch job every day, it calls pg_dump.exe from Java and stores its output to an SQL file. When I'm executing Tomcat from the shell, it ...