+ Responder ao Tópico



  1. #1
    replicante
    Visitante

    Padrão Postgres 7.4 - EXPORTAR e IMPORTAR

    Senhores, HELP!!!!!

    Como eu faço importação e exportação disso!?! Já usei pg_dump,
    pg_dumpall, pg_restore... tudo!... e nada parece funcionar direito...

    alguem manja desse trem ai pra me dar uma ajuda/???

    muito obrigado!


  2. #2

    Padrão Re: Postgres 7.4 - EXPORTAR e IMPORTAR

    ola replicante, eu nao entendi muito bem o que vc quiz dizer com "nao funcionar direito", mas o que eu costumo fazer eh:

    Código :
    $ pg_dump -h host -F p -f arquivo.sql banco_de_dados
    ...
    $ psql -h host
    => \i arquivo.sql
    ...

    E pronto, ta exportado e importado

  3. #3
    Mordecai
    Visitante

    Padrão Re: Postgres 7.4 - EXPORTAR e IMPORTAR

    Eu uso esse script pra fazer backup.

    http://www.vivaolinux.com.br/scripts...hp?codigo=1386

    Tem funcionado muito bem
    []'s

  4. #4

    Padrão Re: Postgres 7.4 - EXPORTAR e IMPORTAR

    Citação Postado originalmente por replicante
    Como eu faço importação e exportação disso!?! Já usei pg_dump,
    pg_dumpall, pg_restore... tudo!... e nada parece funcionar direito...
    O que quer dizer com não funciona direito ?
    Qual o erro que ocorre ?

  5. #5
    replicante
    Visitante

    Padrão Re: Postgres 7.4 - EXPORTAR e IMPORTAR

    entao, o problema é que esta tablea está com o tsearch2 "acoplado" e esse é o problema... achei a documentação do tsearch ensinando como exportar / importar qdo ele está desse jeito, mas ainda nao tive tempo de testar (achei hoje)...

    caso alguem ja tenha usado isso e conheça o caminho das pedras a ajuda é muito bem-vinda... hehehehe

    VALEU!

  6. #6

    Padrão Re: Postgres 7.4 - EXPORTAR e IMPORTAR

    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

  7. #7
    replicante
    Visitante

    Padrão Re: Postgres 7.4 - EXPORTAR e IMPORTAR

    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.

    Abraço!!!


    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

    createdb 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).