What is the most efficient, secure way to pipe the contents of a postgresSQL database into a compressed tarfile, then copy to another machine? This would be used for localhosting development, or backing up to a remote server, using *nix based machines at both ends. ...
Hello. I have a really big database (running on PostgreSQL) containing a lot of tables with sophisticated relations between them (foreign keys, on delete cascade and so on). I need remove some data from a number of tables, but I'm not sure what amount of data will be really deleted from database due to cascade removals. How can I check...
Can I programatically(or whichever way works fine) create the backup of a database, with only the tables I want? I have around 100 tables in my database and I want only 10 tables backup(ofcourse all are interdependant). How can I achieve this? And by the way I have a postgresql database. ...
Do I always have to delete and then create a database to restore it from a pg_dump file? If I don't delete the database, the data being restore is added to the current data in the DB even if some register already are in the database (so the data is duplicated). ...
In short, my question is "why doesn't $libdir work on my PSQL installation." CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d AS '$libdir/liblwgeom', 'BOX2DFLOAT4_in' LANGUAGE c IMMUTABLE STRICT; yields an error could not access file "$libdir/liblwgeom": No such file or directory while CREATE FUNCTION st_box2d_in(cstri...
Hi, While restoring some database backups I noticed that pg_dump is actually using INSERTS rather than COPY. I am not even specifying -d flag but it's still using INSERTS for every database / table I try to dump which is why restores take hours rather than minutes. According to the pg docs pg_dump should use COPY by default but in my c...
Hello, I am creating to pg_dumps, DUMP1 and DUMP2. DUMP1 and DUMP2 are exactly the same, except DUMP2 was dumped in REVERSE order of DUMP1. Is there anyway that I can sort the two DUMPS so that the two DUMP files are exactly the same (when using a diff)? I am using PHP and linux. I tried using "sort" in linux, but that does not work....
I am using batch file to run certain operation in my application. The command I am using does not take password as a parameter instead it prompts for it while running. This is coming in the way of automating this script. I would like to know how I can take password as a parameter and provide to the application when it prompts. ...
I have used pg_dump on one machine and copied result file to another, where I tried to restore it. I believe schema is the same. However, I get: pg_restore: [archiver] input file does not appear to be a valid archive I have done following operations: pg_dump -a -f db.txt dbname and: pg_restore -a -d dbname db.txt What might be w...
Hi, Is it possible to have the postgres database dump(pg_dump) using SQLAlchemy? i can get the dump using pg_dump but I am doing all other db operations using SQLALchemy and thus want to know if this dump operation is also opssible using SQLAlchemy. Any suggestion, link would be of great help. Thanks, Tara Singh ...
I have a decent sized PostgreSQL database (approx 6GB & growing). A full backup/export of the database is done every few hours via cron & pg_dump. Specifically, can I export only the changes to the database since the last export? Or perhaps run a utility that compares the two exports and appends the differences to the original, etc? I...
Is there a way to create a backup of a single table within a database using postgres? And how? Does this also work with the pg_dump command? ...
i use the slony for replication of postgresql database. it work fine some day. After i use the slony command to delete the replication node, pg_dump does not work, the error message is: pg_dump: schema with OID 73033 does not exist Then i delete the name space of slony in pg_namespace, and pg_dump does not work. So i delete the data of...
Dear fellow developers, As subjects, what will happen to the backup file meanwhile there is still many active transaction to our database. Does it export realtime or just partially backups ? thanks in advance for any lights. ...