![]() Several additional options enable you to control what, and how, things get backed up. ![]() I already introduced one option available to pg_dump, the custom file format. However, to summarize, you get a lot more control over exactly what gets restored using pg_restore and the custom dump format instead of the standard SQL. There are a number of other behaviors you can control in pg_restore that don’t apply to a getting started article. Pick and choose individual objects to restore.Use multiple concurrent jobs to make the restore run faster.Define a specific list of objects to restore.Output the contents of the dump file as a list of objects.Restore just the data and not the schema.You will find there are quite a few options if you use the custom file format and pg_restore. While running the SQL scripts of a basic pg_dump command is simple, you don’t have much control. Note that you will initially connect to a different database with the -d option and that the restore will use the database name from the dump file. Running both -clean and -create will result in pg_restore dropping and recreating your database. Pg_restore -d postgres -U postgres -C -c bucustom. If simple database restores are all you’re interested in, then the best tool for the job is pg_dump. With the requirements defined, you can then walk over and pick up the right backup tool. Production systems need point-in-time restores, so you probably want a database backup that requires managing the WAL files. Development systems usually require taking a quick database backup and you’re done. However, that brings up another aspect of a restore strategy, recovery time.Ī recovery strategy must be built to define how you would like to restore and how long you want it to take. In theory, an infinite number of WAL files can be played back to arrive at a point in time. You can configure the size and number of files in the WAL. The WAL is automatically created and maintained by your servers. Point-in-time recoveries are accomplished through the write ahead log (WAL) maintained by PostgreSQL. A point-in-time recovery means you can get the last backup and all the changes to the database since that backup. For example, taking a backup once a day means that, if a problem occurs about 12 hours into the day, you’re going to lose all that data. Generally, most organizations frown on data loss. This is where the point in time restore comes into play. If data or structural changes occurred after the backup that you’d like to recover, you just can’t do it. However, it is limited in that you can only restore to the point of the backup. It’s an easy way to quickly migrate a database to a new server. This kind of restore operation is acceptable for development and test systems. It’s also possible to restore to a point-in-time, but I’m saving the details of how to do that for a future article.Ī simple restore from a backup is meant to get the database back to the state it was in right at the point that a backup was taken. However, to keep the size of this article to something reasonable, and because I haven’t learned everything I need to just yet, I’m going to focus on one restore mechanism: restore from a backup. This article could get into all sorts of permutations about all the different methods for restoring a database. Restoring a database is an enormous topic. So, before you start thinking about backups, you should be thinking about how you want to restore your systems. You can have an infinite number of backups, but if you can’t restore even one of them, that’s nothing but wasted space. In short, you have to take backups, but what’s important is the ability to restore the data. There’s a saying that, as far as I know, originated with Kimberly Tripp: Your backups are only as good as your last restore. Instead of talking immediately about backups, let’s talk about restores. However, I didn’t realize just how big a can of worms I would open by pursuing backups and restores. I don’t see anything in PostgreSQL that will lead me to a different conclusion. One of the first things I worry about when I’m looking at a new system is the status of the backups. Data Types in PostgreSQL: Learning PostgreSQL with Grant.How to back up and restore with PostgreSQL: Learning PostgreSQL with Grant.Creating a Database and Tables in PostgreSQL: Learning PostgreSQL with Grant. ![]() Connecting to PostgreSQL: Learning PostgreSQL with Grant.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |