The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
2answers
17 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
30 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
21 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
20 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
36 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
84 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 ...
3
votes
2answers
373 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
69 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
41 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
43 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
286 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 ...
2
votes
1answer
160 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
42 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
215 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 ...
2
votes
1answer
29 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
928 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
47 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
216 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
159 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 ...
1
vote
1answer
912 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
362 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 ...