0
votes
1answer
21 views

PostgreSQL: After restoring data, unable to use Views (permission denied)

I'm attempting to get a PostgreSQL environment running locally. I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment. The restoration worked fine ...
0
votes
1answer
29 views

Better way to restore to existing pg database

We want to restore to an existing 9.3 pg database. What we ended up doing was: 1) delete the database and recreate again, 2) use pg_restore to restore. 3) reboot the ubuntu server to allow rails ...
0
votes
1answer
51 views

Restore postgres in command line on ubuntu failed

This question may be asked many times. We are trying to move a postgres db from windows (psql 9.3) to ubuntu runing postgres 9.3. Here is what we did: In windows, create backup for db cis with ...
0
votes
1answer
49 views

Postgres database dump and restore on different database [closed]

I have two Postgres databases on the same server, which have the same schemas. The goal is to have DB1 as the production and DB2, as the database that will receive all the data that is migrated from a ...
2
votes
1answer
61 views

Recovering a partial PostgreSQL archive

I have $PGDATA/base but nothing else. It was taken from an installation after the postgreSQL service was shutdown, so is clean. Is there any way to recreate the other elements in $PGDATA that allow ...
1
vote
1answer
86 views

PostgreSQL (9.3) restore won't finish

I've been running a restore job on 38m row table for over 24 hours that doesn't seem to want to come to an end. When I check pg_stat_activity it tells me that the restore job is still running, and the ...
1
vote
2answers
156 views

How to create a blank version of a PostgreSQL database

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my ...
1
vote
1answer
425 views

Restoring PostgreSQL 9.1.9 backup with 9.3.0 results in error [closed]

How can I resolve the following errors in 9.3.0: psql mydb < ~/mydb_2013-10-25.backup.tar results in ... ALTER TABLE ALTER TABLE ALTER TABLE ERROR: could not open file "$$PATH$$/2309.dat" for ...
2
votes
1answer
342 views

pg_dump custom format between postgres versions

I was wondering how stable the custom pg_dump format is between Postgres versions. Can I for example restore a custom format dump created with Postgres 8.x in postgres 9.x? Background: I am building ...
1
vote
0answers
406 views

pg_restore and psql asks password for restoring dump file

This will be probably extreme novice question but I want to restore a .backup dump file. I have tried the following command: pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v ...
0
votes
1answer
305 views

Postgresql: Difference between pg_dump and backup/restore?

Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore ...
3
votes
1answer
1k views

Ensure correct username when using pg_restore

I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this: sudo -u postgres psql -c ...
3
votes
1answer
760 views

How to wait between PostgreSQL DB restore and reindexing?

I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution: Start the pg service Vacuum all DBs (not sure if this is needed) Backup with pg_dumpall Stop the ...
4
votes
2answers
392 views

How to wait for PostgreSQL to be startable / restorable?

I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows: Start the pg service Vacuum all DBs (not sure if this is ...
2
votes
3answers
3k views

Copy PostgreSQL data from one PC to another

I am migrating my server application from the existing system to another system. Unfortunately, the existing system is also the database server. It has data stored. Is it possible to copy this data to ...
4
votes
1answer
3k views

disable constraints before using pg_restore.exe

When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same: ERROR: insert or update on table "someTable" violates foreign key constraint ...
1
vote
1answer
241 views

Questions about PostgreSQL backup/restores

I've inherited a large PostgreSQL 8.4.7 database that is currently not being backup at all. The database itself I believe is around 100GB, I've been reading about the different methods to backup this ...
0
votes
1answer
338 views

Drop and restore PostgresSQL database without losing settings

This seems like a simple, easy question, but I'm very new to PostgreSQL administration and I'm not sure of the best way to do things. I have a PostgreSQL (9.1) database that somehow got corrupted ...
3
votes
1answer
3k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role ...
3
votes
1answer
2k views

Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?

TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why? Context: We run postgresql 8.4, on CentOS 6, ...
2
votes
2answers
2k views

Trouble recovering data from corrupted PostgreSQL tables

I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble. When I do select count(*) One table returns: ERROR: could not ...
1
vote
0answers
139 views

pg_dump dumps casts after views using them [closed]

My small but annoying problem is that the regular backups of a database of ours always list the user-defined casts after those views that would make use of them. For this reason I have to maintain a ...
0
votes
2answers
5k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
3
votes
1answer
1k views

PostgreSQL Scheduled Database Backup

For scheduling a daily back up on a postgres database we execute the following command: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \ > ...
2
votes
1answer
401 views

Postgresql revert back database value

I have postgresql database. My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table. When I get the data file using following commands. select * from ...
2
votes
1answer
1k views

Does my choice of pg_dump format impact restore speed?

According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats." The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the ...