Copia de seguridad, restauración y carga de datos en PostgreSQL y MySQL

Los datos contenidos en las bases de datos relacionales son de los más importante en una aplicación. Hacer copias de seguridad o volcados de forma regular es indispensable para poder restaurar los datos en caso de desastre en el servidor. Las bases de datos proporcionan herramientas para hacer estas copias de seguridad y restaurado tanto de los datos como del esquema de las bases de datos. Las copias de seguridad y restaurado también nos sirven para disponer en nuestro entorno de desarrollo de una base de datos con datos reales con los que probar la aplicación.

En la base de datos PosgreSQL disponemos de los comandos pg_dump para hacer la copia de seguridad o extraer el esquema y los datos y el comando pg_restore para cargarlos en un nuevo servidor u otro esquema. En MySQL disponemos de los comandos mysqldump y depende de como hagamos las copias de seguridad mysqlimport.

PostgreSQL

Los comandos básicos para hacer la copia de seguridad y restauración en PostgreSQL de una base de datos completa son lo siguientes:

1
2
3

$ pg_dump database > database.sql

$ psql -d database -f database.sql

PosgreSQL usando un formato de copia de seguridad personalizado con la opción -Fc permite restaurar posteriormente partes de la base de datos, por ejemplo, una tabla o un índice en concreto.

1
2
3
4

$ pg_dump -Fc database > db.dump

$ pg_restore -d database db.dump
$ pg_restore -U $username --dbname=$dbname --table=$tablename

También es posible separar la definición y los datos de las copias de seguridad con las opciones –schema-only y –data-only.

1
2
3
4
5

$ pg_dump -Fc --schema-only database > database-ddl.dump
$ pg_dump -Fc --data-only database > database-data.dump

$ psql -d database -f database-ddl.sql
$ psql -d database -f database-data.sql

Como las bases de datos pueden llegar a ser de varias decenas de GiB generar las copias de seguridad comprimidas que reducirán su tamaño notablemente. Los volcados con la opción -Fc ya está comprimidos con lo que esto solo es necesario si generamos volcados en archivos .sql.

1
2
3

$ pg_dump database | gzip > database-backup.sql.gz

$ gunzip < database-backup.sql.gz | psql -d database

MySQL

En MySQL los comandos de copia de seguridad y restauración son similares.

1
2

$ mysqldump database > database.sql
$ mysql database < database.sql

Con la opción –extended-insert la importación posterior del volcado será algo más rápida al incluir sentencias insert con múltiples datos de vez de un insert por cada fila.

1
2

$ mysqldump --extended-insert database > database.sql

También es posible separar los datos de la definición del esquema con las opciones –no-data y –no-create-info.

1
2

$ mysqldump --no-data database > database-ddl.sql
$ mysqldump --no-create-info database > database-data.sql

Para generar y restaurar copias de seguridad comprimidas se debe usar el comando gzip y gunzip.

1
2

$ mysqldump database | gzip > database.sql.gz
$ gunzip < database.sql.gz | mysql database

Carga masiva de datos

Si se desean cargar datos de una fuente externa de forma masiva a una base de datos PostgreSQL o MySQL en vez de generar un archivo con varios cientos de miles las sentencias insert es mejor generar un archivo en formato csv o txt con algunos delimitadores y usar el comando COPY en PostgreSQL o el comando LOAD DATA en MySQL complemento de SELECT … INTO. Estos comandos están diseñados para cargar datos de forma masiva en la base de datos con lo que la importación será mucho más rápida.

En ambos casos las exportaciones e importaciones soportan archivos en formato texto y csv con la posibilidad de personalizar los caracteres separadores de las columnas y las columnas en las que importar los datos.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

COPY table TO 'data.txt';

COPY table FROM 'data.txt';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

SELECT * FROM table INTO 'data.txt';

LOAD DATA INFILE 'data.txt' INTO TABLE database.table;