16-05-2006, 21:56 #6
q estranho cara...eu ja usei o tsearch2 e nao me lembro de ter tido problemas para dumpar/importar...
mas de qq forma posta ai se der algum erro
17-05-2006, 07:34 #7replicante
Senhores, os procedimentos BASICOS são esses... mas na migração de uma aplicação chamada GForge (tipo sourceforge) tem mais coisa, muito mais... rs...
Caso alguem esteja lendo isso por conta do GForge pode me procurar q eu ajudo.
Mas o básicão tá ai:
* BACKING UP AND RESTORING DATABASES THAT FEATURE TSEARCH2 *
Never rely on anyone elses instructions to backup and restore a database system, always develop and understand your own methodology, and test it numerous times before you need to do it for real.
The backup and restore procedure has changed over time. This is not meant to be the bible for tsearch2 back up and restore. Please read all sections so you have a complete understanding of some backup and restore issues. Please test your own procedures, and do not rely on these instructions solely.
If you come accross some issues in your own procedures, please feel free to bring the question up on the Open-FTS, and PostgreSQL mailing lists.
* ORIGINAL BACKUP PROCEDURES *
Originally, tsearch2 had problems when using the pg_dump, and or the pg_dumpall utilities. The problem lies within the original use of OIDs for column types. Since OIDs are not consistent accross pg_dumps, when you reload the data values into the pg_ts_dict table, for example, those oids no longer point to anything. You would then end up trying to use a "broken" tsearch2 configuration.
The solution was to backup and restore a database using the tsearch2 module into small unique parts, and then load them in the correct order. You would have to edit the schema and remove the tsearch stored procedure references in the sql file. You would have to load your global objects, then the tsearch2 objects. You had to re-create the tsearch module before restoring your schema so no conflicts would arise. Then you could restore your data (all schemas, and types needed for the data were now available).
The original backup instructions were as follows
1) Backup any global database objects such as users and groups (this step is usually only necessary when you will be restoring to a virgin system)
pg_dumpall -g > GLOBALobjects.sql
2) Backup the full database schema using pg_dump
pg_dump -s DATABASE > DATABASEschema.sql
3) Backup the full database using pg_dump
pg_dump -Fc DATABASE > DATABASEdata.tar
The original restore procedures were as follows
1) Create the blank database
2) Restore any global database objects such as users and groups (this step is usually only necessary when you will be restoring to a virgin system)
psql DATABASE < GLOBALobjects.sql
3) Create the tsearch2 objects, functions and operators
psql DATABASE < tsearch2.sql
4) Edit the backed up database schema and delete all SQL commands which create tsearch2 related functions, operators and data types, BUT NOT fields in table definitions that specify tsvector types. If your not sure what these are, they are the ones listed in tsearch2.sql. Then restore the edited schema to the database
psql DATABASE < DATABASEschema.sql
5) Restore the data for the database
pg_restore -N -a -d DATABASE DATABASEdata.tar
If you get any errors in step 4, it will most likely be because you forgot to remove an object that was created in tsearch2.sql. Any errors in step 5 will mean the database schema was probably restored wrongly.
Issues with this procedure
As I mentioned before, it is vital that you test out your own backup and restore procedures. These procedures were originally adopted from this document's orignal author. Robert John Shepherd. It makes use of the pg_dump custom archive functionality. I am not that familiar with the formatting output of pg_dump, and using pg_restore. I have always had the luxury of using text files (Everything is DATABASE.sql).
One issue not forseen in the case of using a binary dump is the when you have added more than the default tsearch2 configurations. Upon reload of the data it will fail due to duplicate primary keys. If you load the tsearch2 module, and then delete the data loaded by tsearch2 into the configuration tables, the data will restore. The configurations are incorrect because you can not remove the data using OID references from the custom archive.
It would be very simple to fix this problem if the data was not in an archive format. I do believe all of your data would have been restored properly and you can get things working fairly easy. All one would have to do is create the configurations as in the tsearch2.sql file. And then create your custom configurations again.
I have read in the pg_dump man page that if the tar archive format is used, it is possible to limit which data is restored using pg_restore. If anyone has more experience with pg_dump archives, and pg_restore. Please feel free to test and contribute your procedure(s).