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

Escrito por picodotdev el .
planeta-codigo programacion
Enlace permanente Comentarios

Siempre es recomedable tener una copia de seguridad de algo tan importante como suelen ser los datos en una base de datos. Esa copia de seguridad sirve para en caso de desastre restaurar la mayor parte de los datos, también sirven para restaurarlos en otras máquinas diferentes a la principal. Tanto PostgreSQL como MySQL tienen comandos para hacer copias de seguridad y restaurarlas, también tiene opciones para cargar datos de forma masiva de forma rápida y más eficiente que con sentencias SQL.

PostgreSQL

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
postgresql-backup.sh

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
postgresql-backup-format-custom.sh

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
postgresql-schema-data.sh

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
postgresql-compress.sh

MySQL

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

1
2
$ mysqldump database > database.sql
$ mysql database < database.sql
mysql-backup.sh

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

1
$ mysqldump --extended-insert database > database.sql
mysql-backup-extended-insert.sh

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
mysql-schema-data.sh

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
mysql-compress.sh

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 impotaciones soportan archivos en formato texto y csv con la posibilidad de personalizar los caracteres seperadores 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';
postgresql-copy.sh
 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;
mysql-load.sh
Comparte el artículo: