Tracking changes with git
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.