Обсуждение: Updating data: confirmation and question
Before my old server died I ran pg_dumpall on the database there and copied that to my new server. The old database was postgresql-10 and the new one is postgresql-11.5. Am I correct that I can run 'pgsql -f <filename>.sql' and overwrite the existing databases with the newer data? Checking the database names in both I see that I mistakenly used hyphens rather than underscores in one database name. I can change that in the .sql file but have not before learned how/whether I can change a database name using psql. What's the best approach to changing the existing hypephenated name? TIA, Rich
On 10/26/19 8:38 AM, Rich Shepard wrote: > Before my old server died I ran pg_dumpall on the database there and copied > that to my new server. The old database was postgresql-10 and the new > one is > postgresql-11.5. > > Am I correct that I can run 'pgsql -f <filename>.sql' and overwrite the > existing databases with the newer data? That depends on how you ran pg_dumpall. For instance did you use -c?: https://www.postgresql.org/docs/11/app-pg-dumpall.html > > Checking the database names in both I see that I mistakenly used hyphens > rather than underscores in one database name. I can change that in the .sql > file but have not before learned how/whether I can change a database name > using psql. What's the best approach to changing the existing hypephenated > name? This?: https://www.postgresql.org/docs/11/sql-alterdatabase.html ALTER DATABASE name RENAME TO new_name > > TIA, > > Rich > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 26 Oct 2019, Adrian Klaver wrote: > That depends on how you ran pg_dumpall. For instance did you use -c?: Adrian, Yes. Always. > https://www.postgresql.org/docs/11/sql-alterdatabase.html > ALTER DATABASE name RENAME TO new_name Ah, I should have looked. I apologize. Rich
On 10/26/19 10:39 AM, Rich Shepard wrote: > On Sat, 26 Oct 2019, Adrian Klaver wrote: > >> That depends on how you ran pg_dumpall. For instance did you use -c?: > > Adrian, > > Yes. Always. Then the question is, do you really want to overwrite the new database? > >> https://www.postgresql.org/docs/11/sql-alterdatabase.html >> ALTER DATABASE name RENAME TO new_name > > Ah, I should have looked. I apologize. > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 26 Oct 2019, Adrian Klaver wrote: > Then the question is, do you really want to overwrite the new database? Adrian, I want to overwrite the old databases with the new .sql file. I _think_ there's only one database that's changed, but overwriting all won't hurt. Regards, Rich
On 10/26/19 1:39 PM, Rich Shepard wrote: > On Sat, 26 Oct 2019, Adrian Klaver wrote: > >> Then the question is, do you really want to overwrite the new database? > > Adrian, > > I want to overwrite the old databases with the new .sql file. I _think_ > there's only one database that's changed, but overwriting all won't hurt. Just me, but to keep this from being a 'famous last words' moment I would backup up the new(11.5) instance before proceeding. > > Regards, > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 26 Oct 2019, Adrian Klaver wrote: > Just me, but to keep this from being a 'famous last words' moment I would > backup up the new(11.5) instance before proceeding. Adrian, Okay. That makes good sense. Will do a pg_dumpall on the 11.5 cluster. When I'm actively working on a database I do a nightly dump (datestamped). Thanks, Rich