Rails Webmaster's Handbook: useful snippets for a Rails + PostgreSQL server admin

webmaster_handbook.md

Rails Webmaster's Handbook

Snippets and commands I need to use constantly but that I forget every single time when doing server admin work.

This is an opinionated handbook for Ubuntu Server only

Linux

Change machine name

Goal: make the prompt say ubuntu@easily-identifiable-machine-name instead of the deafult EC2 names like ubuntu@ip-172-168-1-41

sudo hostnamectl set-hostname my-identifiable-name

macOS

Sometimes you need to install a new version of Ruby, but it complains about the OpenSSL version or the libffi version or whatever. On a macOS machine using Homebrew, you can just do:

rvm install 3.3.0 --with-openssl-dir=$(brew --prefix openssl) --with-ffi-dir=$(brew --prefix libffi)

To guarantee you're using the right OpenSSL and libffi versions and so the configure step runs without problems.

AWS

Easily see instances' and services' pricing and details

Use CloudCraft to create an empty blueprint, then just navigate betweent the services and see their estimations.

PostgreSQL

Exporting databases and importing dumps back again

Dump the database:

pg_dump database_name > dump_filename.sql

Restore the pg_dump:

psql database_name < dump_filename.sql

Drop a database when there are active connections

The error is:

ERROR: database "database_name" is being accessed by other users DETAIL: There is 1 other session using the database.

First, stop further connections:

REVOKE CONNECT ON DATABASE database_name FROM public;

Then, connect to the database \c database_name

Then, terminate all connections to the database:

SELECT pid, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();

Then you can drop the database.

MySQL

Which database is the heaviest?

SELECT table_schema "DB Name",
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
  FROM information_schema.tables
  GROUP BY table_schema;

Exporting and importing databases

Exporting ALL databases:

mysqldump -u root -p --all-databases > all_db.sql

Exporting SOME databases:

mysqldump -u root -p --databases database1 database2 > some_db.sql

Importing DB dumps:

mysql -u root -p < alldb.sql

Rails / Capistrano

Add an interactive remote Rails console through a Capistrano task

This avoids having to ssh into the server and typing cd /home/rails/apps/myapp && RAILS_ENV=production bundle exec rails console every single time you want to open the remote console.

namespace :rails do
  desc 'Open a rails console `cap [staging] rails:console [server_index default: 0]`'
  task :console do
    server = roles(:app)[ARGV[2].to_i]

    puts "Opening a console on: #{server.hostname}...."

    cmd = "ssh -l #{fetch(:user)} #{server.hostname} -t 'source ~/.rvm/scripts/rvm && cd #{fetch(:deploy_to)}/current && RAILS_ENV=#{fetch(:rails_env)} bundle exec rails console'"

    puts cmd

    exec cmd
  end
end