The most simple way to backup and restore a PostgreSQL database is:
Step 1: Backup database using dump
# su - postgres $ ps_dump database_name > file.sql
Step 2: Preferably drop the database on the new server
$ dropdb database_name
Step 3: Create a new database (in this example with UTF8 encoding and owner database_user)
$ createdb database_name --encoding=UTF-8 -U database_user
Step 4: Import the database backup
$ psql database_name < file.sql
Step 5: (optional, you will need it if you create the database without the -U database_user) Grand all privileges to the desired user (if the user does not exists, create the user first)
psql -d template1 GRANT ALL PRIVILEGES ON DATABASE database_name to database_user; \q
And that’s about it!
Database may refuse to drop if in use. In that case, stopping apache (or other web server) and restarting PostgreSQL will bypass this issue.
Always remember to check the consistency of the backup before dropping the database.
