Postgresql – When migrating all the way down, should I just run VACUUM (FULL) as the last down migration

migrationpostgresql

I use node-pg-migrate and Amazon RDS Postgres on a development environment where I'm the process of building an app.

Sometimes I migrate all the way down so that the database is almost as it was when I created the instance.

I found recently that when I did several migrations up and down again, that I ran out of storage (5GB) after the last up migration. After I migrated fully down not the full 5GB is available. That may be because some persistent objects occupy some space.

Nevertheless, I noticed when I ran VACUUM FULL, that more storage was available.

Update. It turns out that more storage may have also become available due to autovacuum which may have coincided with my VACUUM FULL query within graph accuracy in my first test. See the graph below of the free storage space during a second test where I intentionally waited a bit before running VACUUM FULL.

Should I (still) create a down migration file that runs last and runs a VACUUM FULL query to free up storage space? I suspect no downsides since all my objects and data are gone. And autovacuum will do it anyway within minutes.

Best Answer

Other than taking time and resources to run vacuum full will do no harm.

The autovacuum process will eventually trim empty tables. but tables that are merely sparse will continue to consume disk space until a vacuum full.

Related Solutions

PostgreSQL Initial Database Size – SQL Server and PostgreSQL Fragmentation

No the only thing close to that is when you compile the server with the --with-segsize switch, this might help if your table is taking up more space than a gig and your file system can handle a single file being over a gig. If your inserting 20 gigs it will have to create 20 files if you don't use this switch. If your file system can handle a file over a gig you can just set it to a large value most likely see some benefit, worst case a small benefit.
Take a look at CLUSTER http://www.postgresql.org/docs/9.1/static/sql-cluster.htmland FILLFACTOR http://www.postgresql.org/docs/9.1/static/sql-createtable.html, http://www.postgresql.org/docs/9.1/static/sql-createindex.html

Note that FILLFACTOR can be applied to both tables and indexes.

Sql-server – Migrating a cluster from SQL Server 2005 to SQL Server 2016

This is one of those questions that I love to see. There are a lot of things to add to do to add to migration and upgrade successes and it warms the hear to see the question.

You've already covered a lot of things to consider and I'll add a few thoughts but I am looking forward to seeing other answers with more ideas from others.

I'm not including things you've already included in the great list you have started and knowing me I'll be editing and adding to this answer as I go.

**One disagreement I may have with your approach: Regarding the cluster network name, why not make a new cluster name and just use CNAME to point connections to the old name to the new instance? This lets you make a new name, avoid messing with conflict potential, makes go live night smoother and gives you a clearer and easier rollback. I would do a CNAME approach and change the CNAME once you are sure the old can be taken offline. I may be alone here so I welcome comments supporting that idea or disagreeing.

I see a few areas of focus when it comes to migrating. If you make an "outline" of the phases and outline of the areas to focus on comes to mind: Planning, Pre-Migration Steps, Day of Migration, Post Migration. I'll cover some of those sections here and I am hoping others join in with ideas.

Planning Thoughts

Since you are starting with new hardware. This is a great chance to make sure you are considering all of the best practices that may have been overlooked before. Things like:

Split out your drives. At the very least I'd love to see separate drives for (Your Mileage May Vary here also, really depends on performance profile of your setup):
- Operating System (C drive)
- SQL Server binaries and maybe system DBs excluding TempDB
- User Data Files
- User Log Files
- TempDB data and Log Files
- Local backup and/or misc drive for file imports, staging of data, etc
When formatting your drives for Data files and TempDB specifically, consider doing the 64KB allocation unit size. (Source)
Now is your chance to maybe clean up some of those security holes and flaws that could exist. Look to build with best practices and least access.

Before Migration

Lots of things to do before the migration. Some that come to mind immediately are:

Run the upgrade advisor. SQL Server 2016 made some improvements there and it is a separate download. Look to see what issues may exist.
Do a more thorough test of your DB to see what breaks and what works. Do a test migration and put into 2016 (130) compatibility mode. And do thorough testing. If you don't have a test approach you can look at something like profiling activity and replaying to see what breaks. Test. Test. Test.
Grab the SQL Server and Windows Server 2016 documents about clustering and failover clustering. Look to the checklists and prerequisites carefully.
Ensure you have good transaction log management - why migrate an excessively large LDF file due to poor management.
Make sure you are doing maintenance properly on your databases - especially make sure you are running regular DBCC CHECKDBs - this is a good thing to do before migration or really as part of final migration, but you don't want the first time you do one to be on migration night and find surprises.
Determine success criteria and have a rollback plan (doing a migration to new is great, you can simply rollback any CNAME changes, connection strings and point back to old) in case of issues. And know what issues constitute a rollback and what issues you'll "stay and play" with post migration. Get buy in for this list.
Have a really good backup strategy. Know you can restore. Know where your backup files are, etc.
Look to all server objects. You covered a lot. But don't forget credentials, proxy accounts, alerts, maintenance jobs, SP_Configure settings, etc. A GREAT tool for you to look at that can actually help you remember things is the PowerShell DBATools scripts. These scripts can help you migrate all of these items - including SQL authenticated logins with the correct SIDs for your databases, but they also can help you remember what to migrate when you look through all of the modules. You can look into the scripts here.

Immediately After Migration

Lots more here. And a category of "On migration week" could be added and maybe someone will or I will edit and add later.

Some things here that come to mind straight away:

Have a smoke test / check to verify all came over well.
Change compatibility mode to SQL Server 2016 on all databases if testing proved that out to work. I see this getting missed a lot.
Make sure maintenance and backups are in place. Run a post migration DBCC CHECKDB on the databases.
Make sure your jobs are running.
Configure monitoring and alerting so you don't miss alerts about this server
Once you are satisfied with a limited test, turn the old off and make a CNAME change.