Tracking changes with git

From Sudo Room
Jump to navigation Jump to search

MySQL

Daily backup:

   mysqldump -u mysql_user -pmysql_password --skip-extended-insert mysql_database > /path/to/db_backup_`date --rfc-3339=date`.sql

PostgreSQL

  • need the --inserts flag to act similar to MySQL's --skip-extended-insert
  • Need a .pgpass file to contain the postgresql database user's password (the "no-password" flag below means don't prompt for a password, look for a .pgpass file).
   pg_dump -h localhost -U postgres_user -p port --no-password --inserts dbname

From the manual (man) page for pg_dump:

--inserts
Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be
loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row
causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have
rearranged column order. The --column-inserts option is safe against column order changes, though even slower.