Skip to main content
Database Administrators

Return to Question

deleted 46 characters in body; edited tags; edited title
Source Link
András Váczi
András Váczi
  • 31.6k
  • 13
  • 101
  • 149

Migrating a database from one PostgresSQLPostgreSQL server to another cleanly

I have a Rails app server that uses Postgres for the dbDB backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the server was in production by doing the following:

  1. As user postgres: pg_dump databasename > databasename.sqlpg_dump databasename > databasename.sql
  2. scpscp the databasename.sqldatabasename.sql file over to the new server
  3. As user postgres pn the new server enter psql and create the database and alter ownership
  4. As user postgres run psql databasename < databasename.sqlpsql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system. The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another? The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great. I've done this via mySql before but only done it via m

Migrating a database from one PostgresSQL server to another cleanly

I have a Rails app server that uses Postgres for the db backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the server was in production by doing the following:

  1. As user postgres: pg_dump databasename > databasename.sql
  2. scp the databasename.sql file over to the new server
  3. As user postgres pn the new server enter psql and create the database and alter ownership
  4. As user postgres run psql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system. The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another? The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great. I've done this via mySql before but only done it via m

Migrating a database from one PostgreSQL server to another cleanly

I have a Rails app server that uses Postgres for the DB backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the server was in production by doing the following:

  1. As user postgres: pg_dump databasename > databasename.sql
  2. scp the databasename.sql file over to the new server
  3. As user postgres pn the new server enter psql and create the database and alter ownership
  4. As user postgres run psql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system. The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another? The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great.

Post Migrated Here from serverfault.com (revisions)
Source Link
cz3ch
cz3ch
  • 101
  • 3

Migrating a database from one PostgresSQL server to another cleanly

I have a Rails app server that uses Postgres for the db backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the server was in production by doing the following:

  1. As user postgres: pg_dump databasename > databasename.sql
  2. scp the databasename.sql file over to the new server
  3. As user postgres pn the new server enter psql and create the database and alter ownership
  4. As user postgres run psql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system. The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another? The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great. I've done this via mySql before but only done it via m