Oddbean new post about | logout
▲ ▼
Dushman | 1 years ago (raw) | root | parent | reply | flag +1 
 @b17a3b4f @9046526e @67975218  @jaf 

#!/bin/sh
pg_dump -F c --username dbuser dbname > /path/to/backupdir/whatever-bak`date +%Y-%m-%d`.pgdumpThere, make it run as the postgres user.
▲ ▼
:verified_2:防空識別區𝒔𝒐𝒄𝟶:redsta... | 1 years ago (raw) | root | parent | reply | flag 
 @15066d8d Thanks! This is one of the things on my "to do" list. I was going to try messing with it today, among other things, but then my company decided to fly me up to Boston for the night. @9046526e @67975218 @jaf
▲ ▼
jaf | 1 years ago (raw) | root | parent | reply | flag 
 @b17a3b4f @67975218 @15066d8d @9046526e i live in "boston".
▲ ▼
Dushman | 1 years ago (raw) | root | parent | reply | flag 
 @b17a3b4f @9046526e @67975218 @jaf 
Simplest way to do it if you aren't working with a huge database which you won't be on a small instance
▲ ▼
:verified_2:防空識別區𝒔𝒐𝒄𝟶:redsta... | 1 years ago (raw) | root | parent | reply | flag 
 @15066d8d I certainly hope this database doesn't become huge. @9046526e @67975218 @jaf
▲ ▼
Dushman | 1 years ago (raw) | root | parent | reply | flag 
 @b17a3b4f @9046526e @67975218 @jaf 
Just maintain it and you'll be fine
▲ ▼
:verified_2:防空識別區𝒔𝒐𝒄𝟶:redsta... | 1 years ago (raw) | root | parent | reply | flag 
 @15066d8d Right now I'm mostly just running a script that @9046526e wrote to delete remote files older than 3 weeks. Beyond that, I think PostgreSQL performs autovacuum by default? I don't know, unsure how to really check and find out. Something more to research/read about when I get some extended downtime on my computer. 

Otherwise, I occasionally run VACUUM ANALYZE manually. Might be nice to combine the script you made for pg_dump with one that can perform VACUUM ANALYZE so that the database gets dumped, vacuumed, and analyzed daily + older files get pruned daily + the server reboots daily (right now all my VMs reboot daily). @67975218 @jaf
▲ ▼
Dushman | 1 years ago (raw) | root | parent | reply | flag 
 @b17a3b4f @9046526e @67975218 @jaf 
Only run full vacuums once in a while when you wanna reclaim disk space
▲ ▼
:verified_2:防空識別區𝒔𝒐𝒄𝟶:redsta... | 1 years ago (raw) | root | parent | reply | flag 
 @15066d8d I've never once yet run a VACUUM FULL and I've been warned against its utilization in general, outside of extraordinary circumstances. @9046526e @67975218 @jaf
▲ ▼
5e9ff747 | 1 years ago (raw) | root | parent | reply | flag 
 @15066d8d @b17a3b4f @67975218 @jaf Yes, full vacuum is slow and explicite locks the table while it's working on it, meaning for example, can't record new notes in the database. So be careful with that.
▲ ▼
:verified_2:防空識別區𝒔𝒐𝒄𝟶:redsta... | 1 years ago (raw) | root | parent | reply | flag 
 @9046526e Whenever I run VACUUM ANALYZE and pg_dump I stop Misskey so that no activity is taking place while PostgreSQL does it's things. @15066d8d @67975218 @jaf
▲ ▼
恵真 :hypercat: | 1 years ago (raw) | root | parent | reply | flag 
 @b17a3b4f @9046526e @15066d8d @jaf i leave misskey on for pg_dump with no issues
▲ ▼
:verified_2:防空識別區𝒔𝒐𝒄𝟶:redsta... | 1 years ago (raw) | root | parent | reply | flag 
 @67975218 It's probably fine, I'm just trying to be very cautious. Not like I have hundreds of users upset if there is downtime anyway. @9046526e @15066d8d @jaf
▲ ▼
✙ dcc :pedomustdie: :phear_slackware: | 1 years ago (raw) | root | parent | reply | flag 
 @b17a3b4f @67975218 @15066d8d @jaf @9046526e Use pg repack,  its like vacuum but your db stays online l.