@Rentux Software Consulting: julio 2016

Este artículo explica cómo exportar y restaurar (o importar) bases de datos PostgreSQL desde línea de comandos utilizando las herramientas pg_dump, pg_dumpall y psql.

PostgreSQL, o simplemente "Postgres", es un sistema de gestión de bases de datos orientado a objetos y relacional (ORDBMS) que hace énfasis en la extensibilidad y conformidad con los estándares. Está liberado bajo la licencia free/open source PostgreSQL, similar a la licencia MIT. PostgreSQL está desarrollado por el Grupo de Desarrollo Global de PostgreSQL, el cual consiste en un grupo de voluntarios empleados y supervisados por empresas como Red Hat y EnterpriseDB.

PostgreSQL implementa la mayor parte del estándar SQL:2011, cumple con las propiedades ACID (Atomicity, Consistency, Isolation, Durability), es completamente transaccional (incluyendo todas las sentencias DDL, Data Definition Language), posee vistas extensibles actualizables, tipos de datos, operadores, índices, funciones, agregación, incluye un lenguaje procedural, y tiene un gran número de extensiones de terceros.

PostgreSQL funciona en la mayoría de los sistemas operativos incluyendo GNU/Linux, FreeBSD, Solaris, Microsoft Windows y MacOS X. La gran mayoría de distribuciones GNU/Linux disponen de PostgreSQL en sus repositorios.

El sitio oficial del proyecto es www.postgresql.org.

Este es mi primer artículo dedicado a Postgres, por ello voy a arrancar con algo sencillo: cómo exportar y restaurar bases de datos Postgres desde línea de comandos.

Si se desea exportar una base de datos, ya sea para generar una copia de respaldo (backup) como para importar en otro servidor, es posible utilizar la herramienta pg_dump, la cual vuelca una base de datos como una secuencia de instrucciones SQL en formato de texto plano (al igual que la herramienta mysqldump de MySQL). Por ejemplo, si se desea exportar la base de datos "mibd" al archivo "pg_mibd.sql" utilizando el usuario "postgres", ejecutar:

# pg_dump -U postgres -f pg_mibd.sql mibd

O también:

# pg_dump -U postgres mibd > pg_mibd.sql

Para obtener ayuda sobre pg_dump, ejecutar:

# pg_dump --help                                         
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))                      
  -j, --jobs=NUM               use this many parallel jobs to dump        
  -v, --verbose                verbose mode                               
  -V, --version                output version information, then exit      
  -Z, --compress=0-9           compression level for compressed formats   
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit                  

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump     
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump    
  -E, --encoding=ENCODING      dump the data in encoding ENCODING             
  -n, --schema=SCHEMA          dump the named schema(s) only                  
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)                
  -o, --oids                   include OIDs in dump                           
  -O, --no-owner               skip restoration of object ownership in        
                               plain-text format                              
  -s, --schema-only            dump only the schema, no data                  
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=TABLE            dump the named table(s) only                   
  -T, --exclude-table=TABLE    do NOT dump the named table(s)                 
  -x, --no-privileges          do not dump privileges (grant/revoke)          
  --binary-upgrade             for use by upgrade utilities only              
  --column-inserts             dump data as INSERT commands with column names 
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore       
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)         
  --inserts                    dump data as INSERT commands, rather than COPY  
  --no-security-labels         do not dump security label assignments
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

Si en cambio se desean exportar todas las bases de datos de un servidor PostgreSQL, utilizar la herramienta pg_dumpall:

# pg_dumpall -U postgres > pg_todo.sql

Para obtener ayuda sobre pg_dumpall, ejecutar:

# pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME          output file name
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit                  

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -g, --globals-only           dump only global objects, no databases  
  -o, --oids                   include OIDs in dump                    
  -O, --no-owner               skip restoration of object ownership    
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data               
  -S, --superuser=NAME         superuser user name to use in the dump      
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -x, --no-privileges          do not dump privileges (grant/revoke)       
  --binary-upgrade             for use by upgrade utilities only           
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --inserts                    dump data as INSERT commands, rather than COPY
  --no-security-labels         do not dump security label assignments
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.

Report bugs to <pgsql-bugs@postgresql.org>.

Para importar o restaurar un volcado de bases de datos Postgres desde línea de comandos, se debe utilizar la herramienta psql, la cual funciona como terminal interactiva contra servidores PostgreSQL. Por ejemplo, si se desea restaurar el volcado del archivo "pg_mibd.sql" (utilizando el usuario "postgres") guardando un log en el archivo "pg_mibd.log", ejecutar:

# psql -U postgres < pg_mibd.sql > pg_mibd.log 2>&1

Luego es posible buscar errores en el log mediante:

# grep -i error pg_mibd.log

Para obtener ayuda sobre psql, ejecutar:

# psql --help 
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "root")     
  -f, --file=FILENAME      execute commands from file, then exit             
  -l, --list               list available databases, then exit               
  -v, --set=, --variable=NAME=VALUE                                          
                           set psql variable NAME to VALUE                   
  -V, --version            output version information, then exit             
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)              
  -1 ("one"), --single-transaction                                           
                           execute as a single transaction (if non-interactive)
  -?, --help               show this help, then exit                           

Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file                       
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)           
  -q, --quiet              run quietly (no messages, only query output)    
  -s, --single-step        single-step mode (confirm each query)           
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING                        
                           set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator to zero byte
  -0, --record-separator-zero
                           set record separator to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "root")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

Report bugs to <pgsql-bugs@postgresql.org>.

pgAdmin

Para quienes sientan temor de las herramientas de línea de comandos existe un cliente GUI llamado "pgAdmin".
Una vez instalado, ejecutar pgadmin3: