Skip to main content
Share Your Experience: Take the 2024 Developer Survey
19 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 ...
jjanes's user avatar
  • 40.1k
19 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 ...
Otheus's user avatar
  • 594
18 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 ...
Dorian's user avatar
  • 289
13 votes
Accepted

Deleting old postgresql log files (Ubuntu)

You can use the configuration below; log_truncate_on_rotation = on log_rotation_age = 1d log_filename = 'postgresql-%a.log' log_rotation_size = 0 #just rotate daily This says create a log file with ...
Sahap Asci's user avatar
  • 2,969
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 ...
John Eisbrener's user avatar
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 ...
Hannah Vernon's user avatar
  • 70.1k
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 ...
Aaron Bertrand's user avatar
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,...
Craig Ringer's user avatar
  • 56.6k
9 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 ...
Gene's user avatar
  • 295
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 ...
Brent Ozar's user avatar
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 ...
Erwin Brandstetter's user avatar
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 ...
Brent Ozar's user avatar
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 ...
Solomon Rutzky's user avatar
8 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-...
Abdel P.'s user avatar
  • 196
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 ...
Tibor Karaszi's user avatar
7 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 ...
Ilya's user avatar
  • 116
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. ...
Erwin Brandstetter's user avatar
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. ...
Randi Vertongen's user avatar
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 ...
J.D.'s user avatar
  • 37.8k
6 votes

SQL unused logins

You can capture this information with an Extended Events session: CREATE EVENT SESSION [Audit_Logon] ON SERVER ADD EVENT sqlserver.LOGIN ( SET collect_database_name = (1) ,...
spaghettidba's user avatar
  • 11.3k
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 ...
Strahinja Kustudic's user avatar
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 ...
AMtwo's user avatar
  • 16.2k
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 ...
Hannah Vernon's user avatar
  • 70.1k
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 ...
Scott Hodgin - Retired's user avatar
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 ...
Kin Shah's user avatar
  • 62.3k
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), ...
Brent Ozar's user avatar
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 ...
SqlWorldWide's user avatar
  • 13.2k
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....
John K. N.'s user avatar
  • 17.9k
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 ...
Alberto Morillo's user avatar
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 ...
Laurenz Albe's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible