21
votes
Make Postgres database temporarily read-only (for performing volume snapshots)
After culling answers elsewhere on the internet, I devised a solution. The other answers were in and of themselves, incomplete. So I am presenting an answer here in hopes it will benefit others.
The ...
- 654
20
votes
Accepted
Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?
Work done by an interrupted VACUUM FULL will be entirely lost, as it will simply revert to using the previous version of the table and throw away the in-progress version of the table.
Work done by a ...
- 41.3k
19
votes
Force drop db while others may be connected
Using @filiprem's answer in a my case and simplifying it:
-- Connecting to the current user localhost's postgres instance
psql
-- Making sure the database exists
SELECT * from pg_database where ...
- 299
13
votes
Accepted
Detaching a database permanently
If you detach a database from an instance, you will need to perform an OS-level delete of the file. The safer approach is to drop the database instead.
What I suggest is taking a final backup of the ...
- 9,487
13
votes
Detaching a database permanently
I've upvoted John's answer; I'd just like to add some details about other items that you might want to clean up.
SQL Server Agent jobs and alerts might reference the database. Cleaning them up will ...
- 70.5k
11
votes
Accepted
Rebuild Index not freeing up space
If you are expecting a rebuild to make the data files in the file system smaller, this is not how it works.
In fact, rebuilding can even lead to larger data files on disk, as new pages and extents ...
- 182k
10
votes
Do you have to run nodetool repair on every node?
How it behaves depends on your configuration, what version of Cassandra you use, and how you run the repair command.
If you just run nodetool repair on a single node in a cluster it will repair all ...
- 305
10
votes
Make Postgres database temporarily read-only (for performing volume snapshots)
I think it'd be desirable to have this functionality as an official PostgreSQL feature, personally.
Doing it simply
If you don't want to get your hands dirty with C coding for a PostgreSQL extension,...
- 57.3k
9
votes
Accepted
Why does it take so long to view logs on a server?
Yes, reading logs takes a long time in the log file viewer. A few things to fix it:
Try using xp_readerrorlog with filtered parameters to get just the data you want:
@p1 is the log file you want to ...
- 43.2k
9
votes
Accepted
Can not drop a PostgreSQL database using dropdb?
What you see there with psql \l only means that the Postgres role admin has been granted a couple of privileges by the Postgres role postgres - which is the default superuser and probably the actual ...
- 182k
9
votes
Accepted
Does RE-BUILDing a HEAP cause downtime?
Good news: 150,000 forwarded records isn't actually that bad, depending on what kind of time span we're talking about. Forwarded records are tracked as long as the server is up (with some gotchas ...
- 43.2k
9
votes
Accepted
Postgres VACUUM taking forever
You can see the progress of vacuum with
select * from pg_stat_progress_vacuum;
Here are some tips for vacuuming large tables
https://medium.com/coding-blocks/optimizing-storage-and-managing-cleanup-...
- 206
8
votes
How to rename a PostgreSQL cluster?
Debian/Ubuntu has a pg_renamecluster command (available in "postgresql-common" package) which does just what you need:
renames config and data directory,
renames existing log files,
changes a number ...
- 116
8
votes
Accepted
Dump the buffer pool to disk so it can be loaded after a restart
No, there is no way to restore the buffer pool. Some will suggest pre-filling it by selecting all rows from all tables, but that is just silly given how rare it is for systems to use all rows from all ...
- 69.5k
8
votes
Is there any purpose of Ola Hallengren's SQL Server Index and Statistics Maintenance scripts on sql server running on SAN storage?
The "index optimize" job created by Ola's install script focuses on defragmentation, not statistics update. It will do:
Nothing if the fragmentation level is below 5%
REORG if it is between ...
- 17.9k
7
votes
Accepted
Time taken by VACUUM FULL to reclaim space
It's a busy server of a local social networking website. No time is actually "downtime".
So VACUUM FULL is going to be a problem since it takes an exclusive lock on each table it processes. ...
- 182k
7
votes
Accepted
IndexOptimize - Configuration
Why don't "statistics" options appear in the job's command?
Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G.
...
- 16.5k
7
votes
Accepted
Rebuild of DB fails, yet size of the DB has doubled
As discussed in these Microsoft docs on index maintenance, this is typical behavior for index rebuilds. Microsoft recommends having at least twice as much space available because a rebuild stores a ...
- 39.5k
6
votes
Postgresql 9.6 best settings for large admin work (min_wal and max_wal)
I guess you only want to test if the restoration of the dump works and nothing more, which means you can do some unsafe configuration changes. Let's first start with your settings.
These one are a ...
6
votes
Why does it take so long to view logs on a server?
By default, SQL Server will only roll over the error log when the instance restarts. If you have excellent server uptime (perhaps you only patch & reboot a few times per year), the error log could ...
- 16.3k
6
votes
transaction log growing out of control during maintenance
You should be able to do batch updates to this table without causing the log to grow uncontrollably.
Here is a simple testbed to show how I'd approach this:
USE tempdb;
SET NOCOUNT ON;
IF ...
- 70.5k
6
votes
Backup Performance best practices
Firstly, I'd encourage you to stop using maintenance plans for your backups. Ola Hallengren's maintenance scripts are used by a LOT of people. Simply schedule them using the SQL Agent.
I agree that ...
5
votes
Accepted
SQL Server: What is the difference when backing up via .bak, BACPAC or just copying .mdf files?
the .bak file is far larger than the actual database.
The problem might be that you are just appending the backups to one single file. The default is to appended to the device. Take your backups ...
- 62.4k
5
votes
Maintenance plan don't stop & don't backup all databases
If the job is still running, check what command it's currently executing with Adam Machanic's excellent free stored procedure, sp_WhoIsActive. After installing it (typically in the master database), ...
- 43.2k
5
votes
Accepted
Is there any reason to stop transaction log backups during a maintenance window?
You are absolutely correct. There is no reason to stop Transaction log backup during index maintenance window. It will only make things worse as you already experienced. I had cases where I was doing ...
- 13.6k
5
votes
Accepted
(Ola Hallengren) Deleting log backups older than last FULL backup
Part II of II (had to split my answer)
Doing it better
Considering your RPO and RTO defined by your business, you now might want to alter the parameter of the @CleanupTime to something higher than 0....
- 18.3k
5
votes
Accepted
Azure SQL Database and Database Maintenance
Once you have created an Azure SQL Database you can use all tools listed on the following image to provide maintenance:
Regarding to Ola Hallengren's maintenance scripts, you can install them using ...
- 1,817
5
votes
Auto vacuuming high-write, high-update, and mostly read table types
That question is too broad, but here are some pointers about configuring autovacuum:
For tables that receive bulk inserts, run an explicit VACUUM afterwards or use PostgreSQL v13.
For tables with ...
- 56.4k
5
votes
Is there any purpose of Ola Hallengren's SQL Server Index and Statistics Maintenance scripts on sql server running on SAN storage?
It is true that index fragmentation is mostly a thing in the past, if your database is architected properly, making index maintenance mostly moot these days, especially with modern hardware. One ...
- 39.5k
5
votes
Accepted
How can I run sp_refreshview across all views in my SQL Server database
I'll get to my solution in a minute.
But first off, we can avoid this problem altogether. I'd like to tip my hat to Aaron Bertrand, who helped us understand the benefit of using WITH SCHEMABINDING ...
- 339
Only top scored, non community-wiki answers of a minimum length are eligible