Jon's Wiki - User contributions [en]

MacOS

2026-02-26T11:05:46Z

Johnno: /* Compose Key */

== Compose Key ==

# Install [https://karabiner-elements.pqrs.org/ Karabiner-Elements], e.g. with brew install.
# Fetch the large but comprehensive <code>DefaultKeyBinding.dict</code> file from GitHub, [https://github.com/cmloegcmluin/compose2keybindings here]. This has all the X11 compose key mappings in it, plus a bunch of other nifty combinations.
#. Copy it into the <code>~/Library/KeyBindings</code> directory. You may have to create the directory first.
# In Karabiner-Elements under ''Settings → Simple Modifications'', add the following remapping rules for any USB keyboards:
#* left_command → left_alt
#* left_alt → left_command
#* right_alt → right_command
# In ''Settings → Complex Modifications'', Add the rule to rewrite the Right Command key (now mapped as Right Alt on USB keyboards) to Shift + Ctrl + F13. This rule should look like this: <syntaxhighlight lang="JavaScript">
{
"description": "Send Shift + Ctrl + F13 on Right Command to emulate Compose Key",
"manipulators": [
{
"from": { "key_code": "right_gui" },
"to": [
{
"key_code": "f13",
"modifiers": ["left_control", "left_shift"],
"repeat": false
}
],
"type": "basic"
}
]
}
</syntaxhighlight>
# Possibly restart apps if they don’t immediately pick up changes.

=== References ===

# Goller, A (16 August 2022). [https://www.alexandergoller.com/journal/14210/compose-key-on-macos/ “Compose Key on MacOS”] (blog post), alexandergoller.com
# [https://genesy.github.io/karabiner-complex-rules-generator/#eyJ0aXRsZSI6IlJpZ2h0IENvbW1hbmQgdG8gU2hpZnQgKyBDdHJsICsgZjEzIiwicnVsZXMiOlt7ImRlc2NyaXB0aW9uIjoiU2VuZCBTaGlmdCArIEN0cmwgKyBmMTMgb24gUmlnaHQgQ29tbWFuZCB0byBlbXVsYXRlIENvbXBvc2UgS2V5IChodHRwOi8vbG9sZW5naW5lLm5ldC9ibG9nLzIwMTIvMDYvMTcvY29tcG9zZS1rZXktb24tb3MteCkiLCJtYW5pcHVsYXRvcnMiOlt7InR5cGUiOiJiYXNpYyIsImZyb20iOnsia2V5X2NvZGUiOiJyaWdodF9ndWkifSwidG8iOlt7InJlcGVhdCI6ZmFsc2UsImtleV9jb2RlIjoiZjEzIiwibW9kaWZpZXJzIjpbImxlZnRfY29udHJvbCIsImxlZnRfc2hpZnQiXX1dfV19XX0= “Send Shift + Ctrl + F13 on Right Command to emulate Compose Key”], Gene Sy:Karabiner Complex Rules Generator, via GitHub.
# Hocevar, Sam (17 June 2012). [https://web.archive.org/web/20211020080809/https://lolengine.net/blog/2012/06/17/compose-key-on-os-x “Setting up a real Compose key on Mac OS X”] (blog post), The Lol Engine ''(dead link; archived 20 October 2021 by The Internet Archive)''.

MacOS

2026-02-26T06:22:29Z

Johnno: /* References */

== Compose Key ==

# Install [https://karabiner-elements.pqrs.org/ Karabiner-Elements], e.g. with brew install.
# Fetch the large but comprehensive <code>DefaultKeyBinding.dict</code> file from GitHub, [https://github.com/cmloegcmluin/compose2keybindings here]. This has all the X11 compose key mappings in it, plus a bunch of other nifty combinations.
#. Copy it into the <code>~/Library/KeyBindings</code> directory. You may have to create the directory first.
# In Karabiner-Elements under ''Settings → Simple Modifications'', add the following remapping rules for any USB keyboards:
#* left_command → left_alt
#* left_alt → left_command
#* right_alt → right_command
# In ''Settings → Complex Modifications'', Add the rule to rewrite the Right Command key (now mapped as Right Alt on USB keyboards) to Shift + Ctrl + F13. This rule should look like this:

{
"description": "Send Shift + Ctrl + F13 on Right Command to emulate Compose Key",
"manipulators": [
{
"from": { "key_code": "right_gui" },
"to": [
{
"key_code": "f13",
"modifiers": ["left_control", "left_shift"],
"repeat": false
}
],
"type": "basic"
}
]
}

# Possibly restart apps if they don’t immediately pick up changes.

=== References ===

# Goller, A (16 August 2022). [https://www.alexandergoller.com/journal/14210/compose-key-on-macos/ “Compose Key on MacOS”] (blog post), alexandergoller.com
# [https://genesy.github.io/karabiner-complex-rules-generator/#eyJ0aXRsZSI6IlJpZ2h0IENvbW1hbmQgdG8gU2hpZnQgKyBDdHJsICsgZjEzIiwicnVsZXMiOlt7ImRlc2NyaXB0aW9uIjoiU2VuZCBTaGlmdCArIEN0cmwgKyBmMTMgb24gUmlnaHQgQ29tbWFuZCB0byBlbXVsYXRlIENvbXBvc2UgS2V5IChodHRwOi8vbG9sZW5naW5lLm5ldC9ibG9nLzIwMTIvMDYvMTcvY29tcG9zZS1rZXktb24tb3MteCkiLCJtYW5pcHVsYXRvcnMiOlt7InR5cGUiOiJiYXNpYyIsImZyb20iOnsia2V5X2NvZGUiOiJyaWdodF9ndWkifSwidG8iOlt7InJlcGVhdCI6ZmFsc2UsImtleV9jb2RlIjoiZjEzIiwibW9kaWZpZXJzIjpbImxlZnRfY29udHJvbCIsImxlZnRfc2hpZnQiXX1dfV19XX0= “Send Shift + Ctrl + F13 on Right Command to emulate Compose Key”], Gene Sy:Karabiner Complex Rules Generator, via GitHub.
# Hocevar, Sam (17 June 2012). [https://web.archive.org/web/20211020080809/https://lolengine.net/blog/2012/06/17/compose-key-on-os-x “Setting up a real Compose key on Mac OS X”] (blog post), The Lol Engine ''(dead link; archived 20 October 2021 by The Internet Archive)''.

MacOS

2026-02-26T06:20:27Z

Johnno: Created page with "== Compose Key == # Install [https://karabiner-elements.pqrs.org/ Karabiner-Elements], e.g. with brew install. # Fetch the large but comprehensive <code>DefaultKeyBinding.dic..."

== Compose Key ==

# Install [https://karabiner-elements.pqrs.org/ Karabiner-Elements], e.g. with brew install.
# Fetch the large but comprehensive <code>DefaultKeyBinding.dict</code> file from GitHub, [https://github.com/cmloegcmluin/compose2keybindings here]. This has all the X11 compose key mappings in it, plus a bunch of other nifty combinations.
#. Copy it into the <code>~/Library/KeyBindings</code> directory. You may have to create the directory first.
# In Karabiner-Elements under ''Settings → Simple Modifications'', add the following remapping rules for any USB keyboards:
#* left_command → left_alt
#* left_alt → left_command
#* right_alt → right_command
# In ''Settings → Complex Modifications'', Add the rule to rewrite the Right Command key (now mapped as Right Alt on USB keyboards) to Shift + Ctrl + F13. This rule should look like this:

{
"description": "Send Shift + Ctrl + F13 on Right Command to emulate Compose Key",
"manipulators": [
{
"from": { "key_code": "right_gui" },
"to": [
{
"key_code": "f13",
"modifiers": ["left_control", "left_shift"],
"repeat": false
}
],
"type": "basic"
}
]
}

# Possibly restart apps if they don’t immediately pick up changes.

=== References ===

# Goller, A (16 August 2022). [https://www.alexandergoller.com/journal/14210/compose-key-on-macos/ “Compose Key on MacOS”] (blog post), alexandergoller.com
# [https://genesy.github.io/karabiner-complex-rules-generator/#eyJ0aXRsZSI6IlJpZ2h0IENvbW1hbmQgdG8gU2hpZnQgKyBDdHJsICsgZjEzIiwicnVsZXMiOlt7ImRlc2NyaXB0aW9uIjoiU2VuZCBTaGlmdCArIEN0cmwgKyBmMTMgb24gUmlnaHQgQ29tbWFuZCB0byBlbXVsYXRlIENvbXBvc2UgS2V5IChodHRwOi8vbG9sZW5naW5lLm5ldC9ibG9nLzIwMTIvMDYvMTcvY29tcG9zZS1rZXktb24tb3MteCkiLCJtYW5pcHVsYXRvcnMiOlt7InR5cGUiOiJiYXNpYyIsImZyb20iOnsia2V5X2NvZGUiOiJyaWdodF9ndWkifSwidG8iOlt7InJlcGVhdCI6ZmFsc2UsImtleV9jb2RlIjoiZjEzIiwibW9kaWZpZXJzIjpbImxlZnRfY29udHJvbCIsImxlZnRfc2hpZnQiXX1dfV19XX0= “Send Shift + Ctrl + F13 on Right Command to emulate Compose Key”], Gene Sy:Karabiner Complex Rules Generator, via GitHub.
# However, Sam (17 June 2012). [https://web.archive.org/web/20211020080809/https://lolengine.net/blog/2012/06/17/compose-key-on-os-x “Setting up a real Compose key on Mac OS X”] (blog post), The Lol Engine ''(dead link; archived 20 October 2021 by The Internet Archive)''.

Hacks

2026-01-09T01:26:51Z

Johnno: /* Using rsync with FAT volumes (USB sticks and SD cards) */

== Image renaming by create date ==

When you have a bunch of files from your old Android phone with a timestamp filename (e.g. 1388042328550.jpg), you can rename them thus:

for f in 1*.jpg; do
mv $f $(date +IMG_%Y%m%d_%H%M%S.jpg -d @$(echo $f|cut -c 1-10))
done

Otherwise, we can fish out the Create Date from the EXIF data, using exiftool:

exiftool '-FileName<CreateDate' -d %Y%m%d_%H%M%S%%-c.%%e .

== Use etckeeper ==

It saves your bacon. Store changes to the {{path|/etc}} directory in git, uses hooks to commit changes when packages are updated, puppet runs, etc. Stash your own changes with useful commit messages. Look up stuff in history when something goes wrong, figure out what changes happened when packages are added or updated.

=== Fix the daily commit bug ===

Unfortunately since about 18.04 there is a bug where a systemd timer and a cron.daily task compete to commit a daily commit if needed; unfortunately one of them doesn't bother to check the {{path|etckeeper.conf}} file to see whether you've disabled it. If you have <code>DISABLE_DAILY_AUTOCOMMITS=1</code> in your config file, then you probably don't want daily auto commits. If this is so, run this to disable the systemd timer:

systemctl disable --now etckeeper.timer

== Repair broken or rotated phone videos ==

You can fix broken video .tmp files from your Android phone using [https://github.com/ponchio/untrunc/ untrunc]. This will create a fixed.mp4 file in cwd:

untrunc working_video_from_the_same_camera.mp4 broken.mk4.tmp

Did your phone unhelpfully rotate your video? Fix the metadata:

ffmpeg -i original.mp4 -acodec copy -vcodec copy -metadata:s:v:0 rotate=0 unrotated.mp4

Or, did you actually film your shit the wrong way up? You'll need to re-encode it:

ffmpeg -i original.mp4 -acodec copy -vcodec libx264 -crf 20 -metadata:s:v:0 rotate=0 unrotated.mp4

== Export your photos ==

Before uploading your photos, remove all the EXIF tags, and reduce the resolution; nobody needs 5000x4000 pixels, much less corporations hoovering up your stuff to put in adverts.

mkdir tmp
for f in $.jpg; do
convert -resize 1600x1200 $f tmp/$f
exiftool -all= -P -overwrite_original tmp/$f
done

== Clean out your revoked and expired PGP keys ==

Spring-clean your gpg database, make it go faster. Just go:

gpg --delete-keys $(gpg --list-public-keys|grep -PB1 'expired|revoked'|grep '^\s')

Obviously. If you can't be arsed confirming each key, add <tt>--batch --yes</tt> to the first gpg call.

== Firefox opening your shit in gedit ==

I don't know, who thought that was a good idea? Jesus.

sed -i 's#.*octet-stream.*#application/octet-stream=xdg-open#' ~/.local/share/applications/mimeapps.list ~/.config/mimeapps.list

== GNOME not sorting your folders first ==

It's not in a dialog anywhere, and a pretty lame default behaviour.
dconf write /org/gnome/nautilus/preferences/sort-directories-first true

== GNOME not dealing with WebP images ==

I mean, it's 2021 and WebP is only what, ten years old? Jesus. See solution on [https://askubuntu.com/a/617068 AskUbuntu].

== Restoring huge databases to PostgreSQL ==

'''''See also:''''' ''[[PostgreSQL]]''

Skip some huge tables by dumping the TOC, removing culprit large tables, and restore with the edited TOC, e.g.

pg_restore -l huge.dump > TOC
# edit the TOC file...
pg_restore -L TOC -d dbname huge.dump

== Output PostgreSQL queries to CSV ==

COPY (SELECT <query> ...) TO STDOUT CSV HEADER;

== Turn off the shitty default "visual" mouse mode in vim ==

Add this to <code>/etc/vim/vimrc.local</code> file:<ref>Böhnke, H (16 March 2019). [https://unix.stackexchange.com/a/506723 Answer 506723] on "Disabling mouse support in vim in a gnome-terminal environment", Unix & Linux Stack Exchange.</ref>

" Use skip_defaults_vim from the master /etc/vim/vimrc file
if ! exists('skip_defaults_vim')
" Source the defaults file manually from here
source $VIMRUNTIME/defaults.vim
endif

" Avoid loading the defaults twice
let g:skip_defaults_vim = 1

" Revert any unwanted changes the defaults file introduced
set mouse=

== Too many leftover git branches merged into master? Nukem! ==

This will remove any branches on your local git repo that have been merged to master.

for i in $(for b in $(git branch|grep -v '\*'); do git branch $b --merged master; done); \
do git branch -D $i; \
done

== Move your MySQL databases ==

Used to be you could move your /var/lib/mysql directory somewhere else and symlink it. You can, but in newer distros with Apparmor you'll need to declare it:

# In /etc/apparmor.d/tunables/alias
alias /var/lib/mysql/ -> /mnt/wherever/mysql/,

Note the trailing comma.

== Split audio CD rips using the cue file ==

Single-file FLAC or Ape files of a CD can be split up using the .cue file for both the timestamps and the tagging data. Also, install flacon (from a PPA).

sudo apt install shntool
shnsplit -o flac cdimage.flac -f cdimage.cue -t '%n.%t'

You may need to do tags again, if they didn't stick:

sudo apt install cuetools
cuetag cdimage.cue *.flac

== Ditch Snap ==

=== 1. Install Firefox properly ===

Ubuntu 22.04 replaces the Firefox Debian package with a pseudo-package that installs it using Snap. This is no longer optional, and it is also stupid, annoying, slow, and potentially insecure for many reasons.<ref>Brandon Hopkins. [https://techhut.tv/flatpak-vs-snap-vs-appimage/ "Flatpak vs. Snap vs. AppImage - Linux Packaging Benchmarks"] TechHut, April 2022.</ref> Several Firefox extensions that need to talk to other applications don't currently work in Snap installed Firefox, including things like Zotero and Keepass.

It also completely fails to find and import any existing Firefox profile. Snap apps for whatever stupid reason keep settings in a hard-coded ~/snap folder. Who thought that was a good idea? What's wrong with ~/.config and/or ~/.local, or couldn't they even use ~/.snap? Good grief.

Luckily, we can use the Mozilla Team PPA instead.<ref>Ji M. [https://ubuntuhandbook.org/index.php/2022/04/install-firefox-deb-ubuntu-22-04/ "How to Install Latest Firefox as classic Deb in Ubuntu 22.04"] Ubuntu Handbook, April 2022.</ref> In summary:
<syntaxhighlight lang="bash">
# Remove Firefox entirely:
snap remove firefox
apt remove firefox

# Add the Mozilla Team PPA and install:
add-apt-repository ppa:mozillateam/ppa
apt update
apt install -t 'o=LP-PPA-mozillateam' firefox

# Pin to the Mozilla Team PPA so we never get the Snap pseudo-package
echo 'Package: firefox*
Pin: release o=LP-PPA-mozillateam
Pin-Priority: 501' > /etc/apt/preferences.d/mozillateam-ppa.pref
</syntaxhighlight>

=== 2. Remove snap ===

You can also remove snap entirely as follows:

<syntaxhighlight lang="bash">
# get a list of things installed with snap:
snap list

# remove them one by one:
snap uninstall ''program1''
snap uninstall ''program2''

# once there's nothing left, remove snap:
apt purge snapd

# then prevent it ever being re-installed with a pin preference:
echo 'Package: snapd
Pin: release a=*
Pin-Priority: -10' > /etc/apt/preferences.d/no-snap.pref
</syntaxhighlight>

==Fancy PostgreSQL prompts==

For a coloured prompt including what user, connection, and host you're on, stick this in ~/.psqlrc or /etc/postgresql-common/psqlrc:

\set PROMPT1 '%[%033[1m%](%n@%[%033[1;35m%]%M%[%033[m%]%[%033[1m%] on %[%033[1;31m%]%`hostname`%[%033[m%]%[%033[1m%]) \n%/%R%#%[%033[m%] '
\set PROMPT2 '%[%033[1m%]%/%R%#%[%033[m%] '
\set PROMPT3 '%[%033[1m%]>>%[%033[m%] '

Escape codes use '%' character, documented here: [https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PROMPTING Psql Prompts].

==Using rsync with FAT volumes (USB sticks and SD cards)==

You might have noticed that all your files have today's timestamp on them when using rsync thus:
rsync -av /path/to/my/stuff /mnt/usb-stick/

The -a option means -rlDtpog, which is recursive, include links and devices, preserving timestamps, permissions, owner and group. Which is what you want most of the time. Trouble is, FAT partitions have no concept of permissions, owners and groups, so you need to use:
rsync -rtlDv /path/to/my/stuff /mnt/usb-stick/

But that's probably not all, because rsync will probably complain that it can't set the timestamp. You'll need to mount your USB stick so it ignores a user check that FAT can't enforce:
mount -o remount,allow_utime=2 /mnt/usb-stick

==Update your groups without logging out and in again==

When adding yourself to some new group, refresh your current shell session's idea of what groups you're in:
sudo usermod -aG ''somegroup'' ''me''
newgrp ''somegroup''

==Notes==

<references/>

NextCloud

2025-09-16T23:47:22Z

Johnno: /* NextCloud Office */

Assumes Ubuntu 20.04, stable20 branch, and installing in {{code|/var/www/nextcloud}} for a cloud.'''example.com''' URL (c. December 2020).

== Prerequisites ==

Install some stuff first. This NextCloud recipe uses [[PostgreSQL]], Redis, Apache 2.4, FastCGI with PHP FPM, and Dehydrated for [[Letsencrypt]] SSL certificate maangement.

sudo apt install apache2 libapache2-mod-fcgid \
php7.4-{bcmath,bz2,curl,gd,fpm,gmp,intl,json,mbstring,opcache,pgsql,tidy,xmlrpc,xsl,zip}
php-{imagick,redis} composer dehydrated postgresql redis-server

;NOTE: NextCloud works fine under nginx, I just can't be bothered maintaining what goes in {{code|.htaccess}} which NextCloud expects to be writeable to keep itself updated.

== Configuring Apache and PHP ==

: ''See the [[PHP]] page for setting up PHP with Apache, FPM and Event MPM.''

=== Add a virtual host for NextCloud ===

It should look something like this:

<syntaxhighlight lang="apache">
<VirtualHost *:443>
ServerName cloud.example.com
DocumentRoot /var/www/nextcloud

<Directory "/var/www/nexcloud">
Options FollowSymLinks MultiViews
AllowOverride All
Require all granted
Satisfy Any
SetEnv HOME /var/www/nextcloud
SetEnv HTTP_HOME /var/www/nextcloud

<IfModule mod_dav.c>
Dav off
</IfModule>
</Directory>

SSLEngine On
SSLCertificateFile ...
</VirtualHost>
</syntaxhighlight>

=== PHP settings ===

Enable the opcache, increase the memory limit, and increase the maximum POST and file upload size so you can upload large files.

== Create a database ==

Create a database in [[PostgreSQL]]:

sudo -u postgres createuser -SDRP ''<nextcloud-user>''
sudo -u postgres createdb -E UTF8 -O ''<nextcloud-user>'' ''<nextcloud-dbname>''

== Install NextCloud ==

We can just clone it from git:

git clone <nowiki>https://</nowiki>github.com/nextcloud/server.git nextcloud
cd nextcloud
git checkout stable20 ''# or whatever the latest stable version is''
git submodule update --init --recursive
chown www-data:www-data config apps .htaccess

Create a directory for the cache and user data, which should not reside under the web root:

mkdir -p /var/lib/nextcloud/data /var/cache/nextcloud
chown -R www-data:www-data /var/lib/nextcloud /var/cache/nextcloud

== Configure NextCloud ==

Edit the configuration file in {{code|config/config.php}} and check or consider these settings, and add the database and SMTP connection details:

<syntaxhighlight lang="php">
$config => [
# ...
#
'passwordsalt' => "<very long random string>",
'secret' => "<a different very long random string>",
'trusted_domains' => [
0 => "cloud.example.com",
],
'datadirectory' => '/var/lib/nextcloud/data',
'overwrite.cli.url' => '<nowiki>https://</nowiki>cloud.example.com/',
'htaccess.RewriteBase' => '/',

# Database connection details
'dbtype' => 'pgsql',
'dbhost' => 'localhost',
'dbuser' => "<nextcloud-dbuser>",
'dbpassword' => "<password>",
'dbname' => "<nextcloud-dbname>",
'dbtableprefix' => 'oc_',

# Force all URLs to use SSL or it may trip up things like OAuth
'overwriteprotocol' => 'https',

# Use Redis and caching to make everything go faster
'memcache.local' => '\\OC\\Memcache\\Redis',
'memcache.locking' => '\\OC\\Memcache\\Redis',
'redis' => [
'host' => 'localhost',
'port' => 6379,
],
'cache_path' => '/var/cache/nextcloud',

# Send email details
'mail_smtpmode' => 'smtp',
'mail_smtpsecure' => 'tls',
'mail_sendmailmode' => 'smtp',
'mail_from_address' => "nexcloud@example.com",
'mail_domain' => "example.com",
'mail_smtpauthtype' => 'PLAIN',
'mail_smtpauth' => 1,
'mail_smtphost' => "mail.example.com",
'mail_smtpport' => 587,
'mail_smtpname' => "<emailuser>",
'mail_smtppassword' => "<emailpassword>",

# ...
]
</syntaxhighlight>

Then navigate to the URL in the browser to install (or run the NextCloud {{code | ./occ}} tool from the command line).

;Note: Make sure that the directory used for {{code|cache_path}} exists and is writeable, or you will get weird seemingly unrelated 403 MKCOL errors to do with [https://docs.nextcloud.com/server/26/admin_manual/configuration_files/big_file_upload_configuration.html chunked uploads].

=== Clean URLs ===

For clean URLs, make sure {{code | AllowOverride All}} is set for the parent directory; the {{code | /var/www}} definition is in the main {{code | /etc/apache2/apache.conf}}. Then run the follwing OCC command to enable the rewrite rules:

sudo -u www-data ./occ maintenance:update:htaccess

=== Previews of other file types ===

Edit {{code|/etc/ImageMagick/policy.xml}} and remove this line to get PDF previews to work:

<syntaxhighlight lang="xml">
<policy domain="coder" rights="none" pattern="PDF" /> 
</syntaxhighlight>

Then add the following to {{code|config.php}} to get lots of other useful file previews:

<syntaxhighlight lang="php">
'enabledPreviewProviders' => [
# ...
8 => 'OC\\Preview\\MarkDown',
9 => 'OC\\Preview\\Movie',
10 => 'OC\\Preview\\PDF',
11 => 'OC\\Preview\\OpenDocument',
12 => 'OC\\Preview\\MSOfficeDoc',
13 => 'OC\\Preview\\MSOffice2003',
14 => 'OC\\Preview\\MSOffice2007',
],
</syntaxhighlight>

=== Photos ===

Photos replaces the Gallery app, and is installed separately through the NC store or you can use git to manage it as a submodule. If it doesn't work you might need to build its JavaScript resources by running {{code|make}} in its {{code|apps/photos}} directory. Alternatively you can resuscitate the Gallery app, which stopped working after NextCloud version 17, by updating its JavaScript dompurify library to a [https://raw.githubusercontent.com/nextcloud/gallery/ecb26a812f30a3f516964808ccfa6430be09b45e/js/vendor/dompurify/src/purify.js newer version] and bumping the app max version in {{code|apps/galleryappinfo/info.xml}} as follows (See also [https://github.com/nextcloud/gallery/pull/570/ pull request #570]):

<nextcloud min-version="17" max-version="'''20'''"/>

=== NextCloud Talk ===

'''''Note:''''' ''A TURN server can also be used for other things, like your own [https://matrix-org.github.io/synapse/v1.41/turn-howto.html Matrix] chat server.''

To be at all useful, you need a TURN server. This is easiest achieved by installing and correctly configuring coturn, which also acts as a STUN server. STUN is for clients to figure out their external IP address, and TURN is for relaying UDP packets, e.g. for WebRTC.

sudo apt install coturn

For it to actually do anything (it is disabled by default!) you need to edit {{code|/etc/default/coturn}} and make sure it contains the following, and is not commented out:

TURNSERVER_ENABLED=1

Then in {{code|/etc/turnserver.conf}} you need to configure its domain name, IP addresses and ports, SSL certificate, and how TURN authenticates. See GitHub for a fully documented example [https://github.com/coturn/coturn/blob/master/examples/etc/turnserver.conf turnserver.conf] file. This example uses the same domain as the NextCloud instance, on the standard STUN and TURN ports, but if you want to host it on port 443 to support people behind restrictive firewalls, you will need a different subdomain and/or IP address:

<syntaxhighlight lang="ini">
server-name=cloud.example.com
realm=cloud.example.com

# When hosted on an internal LAN or DMZ, it might look like this:
listening-ip=192.168.1.100
relay-ip=192.168.1.100
external-ip=123.45.67.8

# Or use the short-hand:
external-ip=123.45.67.8/192.168.1.100

# Specify listening ports; originally 3478 was unencrypted, and 5349 was TLS,
# but now TLS is supported on both. It is possible to listen on 443, which will
# help to provide connectivity for people behind port-limited firewalls; this will
# conflict with any HTTPS web hosting on the same server though, unless you set
# up a second IP address for it.
listening-port=3478
tls-listening-port=5349
#tls-listening-port=443

# Specify a UDP port range and ensure they are exposed through the firewall/router
min-port=53490
max-port=59999

# SSL setup;
cert=/path/to/cloud.example.com/fullchain.cert
pkey=/path/to/cloud.example.com/privkey.pem
no-tlsv1
no-tlsv1_1

# To use with NextCloud, set a shared secret here.
use-auth-secret
static-auth-secret=your-shared-secret-string-here
stale-nonce=600

# Limit STUN amplification/gain attacks (coturn 4.5.2+)
no-stun-backward-compatibility
response-origin-only-with-rfc5780

# RFC-5780 support requires two listening IP addresses and is for serious-cat
# usage, so you can safely disable it for a small home NextCloud instance.
no-rfc5780
</syntaxhighlight>

Note that the coturn user can't read certificates in <code>/etc/ssl/letsencrypt</code> so you will need a deploy hook script to copy them safely to somewhere it can read them:

<syntaxhighlight lang="sh">
#!/bin/sh
# /etc/letsencrypt/renewal-hooks/deploy/coturn
set -e
for domain in $RENEWED_DOMAINS; do
case $domain in
cloud.example.com)
certs=/etc/coturn/certs
fullchain="${certs}/${domain}_fullchain.pem"
key="${certs}/${domain}_privkey.pem"

# certificate and private key files are never world readable
umask 077
cp "${RENEWED_LINEAGE}/fullchain.pem" "$fullchain"
cp "${RENEWED_LINEAGE}/privkey.pem" "$key"

chown turnserver:turnserver "$fullchain" "$key"
chmod 400 "$fullchain" "$key"

systemctl restart coturn > /dev/null
;;
esac
done
</syntaxhighlight>

In your NextCloud Administration section, you can now configure Talk to use the STUN/TURN server, using your server URLs, and provide the same shared secret you configured above. For maximum compatibility, use both UDP and TCP, and both turn and turns (TLS) connections:

stun: '''cloud.example.com''':5349
turn and turns: '''cloud.example.com''':5349

Test your server [https://webrtc.github.io/samples/src/content/peerconnection/trickle-ice/ here]. If STUN is working, you should get {{code|srflx}} candidates, and {{code|relay}} candidates if TURN is working.

Sources and further reading:
* NextCloud Talk documentation: [https://nextcloud-talk.readthedocs.io/en/latest/TURN/ TURN], [https://nextcloud-talk.readthedocs.io/en/latest/coturn coturn]
* NextCloud Help, [https://help.nextcloud.com/t/howto-setup-nextcloud-talk-with-turn-server/30794 1] [https://help.nextcloud.com/t/nextcloud-talk-app-could-not-establish-a-connection-a-turn-server/131069/31 2]
* Stack Overflow [https://stackoverflow.com/questions/42801361/install-rfc5766-turn-server-trying-to-bind-fd-to-ip-address-errno-99 1]
* [https://ourcodeworld.com/articles/read/1526/how-to-test-online-whether-a-stun-turn-server-is-working-properly-or-not Testing STUN/TURN]
* Serverfault, question [https://serverfault.com/questions/849683/how-to-setup-coturn-with-letsencrypt #849683].

=== NextCloud Office ===

Install Collabora Office Online (COOL) which emerged from the smoking ruins of LibreOffice Online (LOOL):

apt install coolwsd code-brand

Use an Apache reverse proxy to handle the host and SSL, disable SSL in WOPI setup; restrict NextCloud and coolwsd requests to their respective endpoints, IP addresses; see [https://c-nergy.be/blog/?p=18055 "Install CODE on NextCloud on Ubuntu 22.04"]

For some reason the Debian package on Ubuntu 24.04 does not install the Apparmor profile, which prevents it from starting (unable to mount unprivileged child user namespaces).

''# AppArmor policy for coolwsd:''
''# /etc/apparmor.d/usr.bin.coolwsd''
abi <abi/4.0>,
include <tunables/global>
profile coolwsd /usr/bin/coolwsd flags=(unconfined) {
userns,
include if exists <local/coolwsd>
}

You may also need to set <code>kernel.unprivileged_userns_clone=1</code> in sysctl if it still doesn't work.

== Maintenance ==

Keep the git checkout of NextCloud and its submodules up to date with git, and run the OCC upgrade command:

cd /var/www/nextcloud
git pull
git submodule update --init --recursive
sudo -u www-data ./occ upgrade

=== Cron job ===

In the administrator settings select cron rather than other methods, and put this in {{code|/etc/cron.d/nextcloud}}

*/5 * * * * www-data /usr/bin/php /var/www/nextcloud/cron.php

NextCloud

2025-09-16T23:35:00Z

Johnno: /* NextCloud Talk */

Assumes Ubuntu 20.04, stable20 branch, and installing in {{code|/var/www/nextcloud}} for a cloud.'''example.com''' URL (c. December 2020).

== Prerequisites ==

Install some stuff first. This NextCloud recipe uses [[PostgreSQL]], Redis, Apache 2.4, FastCGI with PHP FPM, and Dehydrated for [[Letsencrypt]] SSL certificate maangement.

sudo apt install apache2 libapache2-mod-fcgid \
php7.4-{bcmath,bz2,curl,gd,fpm,gmp,intl,json,mbstring,opcache,pgsql,tidy,xmlrpc,xsl,zip}
php-{imagick,redis} composer dehydrated postgresql redis-server

;NOTE: NextCloud works fine under nginx, I just can't be bothered maintaining what goes in {{code|.htaccess}} which NextCloud expects to be writeable to keep itself updated.

== Configuring Apache and PHP ==

: ''See the [[PHP]] page for setting up PHP with Apache, FPM and Event MPM.''

=== Add a virtual host for NextCloud ===

It should look something like this:

<syntaxhighlight lang="apache">
<VirtualHost *:443>
ServerName cloud.example.com
DocumentRoot /var/www/nextcloud

<Directory "/var/www/nexcloud">
Options FollowSymLinks MultiViews
AllowOverride All
Require all granted
Satisfy Any
SetEnv HOME /var/www/nextcloud
SetEnv HTTP_HOME /var/www/nextcloud

<IfModule mod_dav.c>
Dav off
</IfModule>
</Directory>

SSLEngine On
SSLCertificateFile ...
</VirtualHost>
</syntaxhighlight>

=== PHP settings ===

Enable the opcache, increase the memory limit, and increase the maximum POST and file upload size so you can upload large files.

== Create a database ==

Create a database in [[PostgreSQL]]:

sudo -u postgres createuser -SDRP ''<nextcloud-user>''
sudo -u postgres createdb -E UTF8 -O ''<nextcloud-user>'' ''<nextcloud-dbname>''

== Install NextCloud ==

We can just clone it from git:

git clone <nowiki>https://</nowiki>github.com/nextcloud/server.git nextcloud
cd nextcloud
git checkout stable20 ''# or whatever the latest stable version is''
git submodule update --init --recursive
chown www-data:www-data config apps .htaccess

Create a directory for the cache and user data, which should not reside under the web root:

mkdir -p /var/lib/nextcloud/data /var/cache/nextcloud
chown -R www-data:www-data /var/lib/nextcloud /var/cache/nextcloud

== Configure NextCloud ==

Edit the configuration file in {{code|config/config.php}} and check or consider these settings, and add the database and SMTP connection details:

<syntaxhighlight lang="php">
$config => [
# ...
#
'passwordsalt' => "<very long random string>",
'secret' => "<a different very long random string>",
'trusted_domains' => [
0 => "cloud.example.com",
],
'datadirectory' => '/var/lib/nextcloud/data',
'overwrite.cli.url' => '<nowiki>https://</nowiki>cloud.example.com/',
'htaccess.RewriteBase' => '/',

# Database connection details
'dbtype' => 'pgsql',
'dbhost' => 'localhost',
'dbuser' => "<nextcloud-dbuser>",
'dbpassword' => "<password>",
'dbname' => "<nextcloud-dbname>",
'dbtableprefix' => 'oc_',

# Force all URLs to use SSL or it may trip up things like OAuth
'overwriteprotocol' => 'https',

# Use Redis and caching to make everything go faster
'memcache.local' => '\\OC\\Memcache\\Redis',
'memcache.locking' => '\\OC\\Memcache\\Redis',
'redis' => [
'host' => 'localhost',
'port' => 6379,
],
'cache_path' => '/var/cache/nextcloud',

# Send email details
'mail_smtpmode' => 'smtp',
'mail_smtpsecure' => 'tls',
'mail_sendmailmode' => 'smtp',
'mail_from_address' => "nexcloud@example.com",
'mail_domain' => "example.com",
'mail_smtpauthtype' => 'PLAIN',
'mail_smtpauth' => 1,
'mail_smtphost' => "mail.example.com",
'mail_smtpport' => 587,
'mail_smtpname' => "<emailuser>",
'mail_smtppassword' => "<emailpassword>",

# ...
]
</syntaxhighlight>

Then navigate to the URL in the browser to install (or run the NextCloud {{code | ./occ}} tool from the command line).

;Note: Make sure that the directory used for {{code|cache_path}} exists and is writeable, or you will get weird seemingly unrelated 403 MKCOL errors to do with [https://docs.nextcloud.com/server/26/admin_manual/configuration_files/big_file_upload_configuration.html chunked uploads].

=== Clean URLs ===

For clean URLs, make sure {{code | AllowOverride All}} is set for the parent directory; the {{code | /var/www}} definition is in the main {{code | /etc/apache2/apache.conf}}. Then run the follwing OCC command to enable the rewrite rules:

sudo -u www-data ./occ maintenance:update:htaccess

=== Previews of other file types ===

Edit {{code|/etc/ImageMagick/policy.xml}} and remove this line to get PDF previews to work:

<syntaxhighlight lang="xml">
<policy domain="coder" rights="none" pattern="PDF" /> 
</syntaxhighlight>

Then add the following to {{code|config.php}} to get lots of other useful file previews:

<syntaxhighlight lang="php">
'enabledPreviewProviders' => [
# ...
8 => 'OC\\Preview\\MarkDown',
9 => 'OC\\Preview\\Movie',
10 => 'OC\\Preview\\PDF',
11 => 'OC\\Preview\\OpenDocument',
12 => 'OC\\Preview\\MSOfficeDoc',
13 => 'OC\\Preview\\MSOffice2003',
14 => 'OC\\Preview\\MSOffice2007',
],
</syntaxhighlight>

=== Photos ===

Photos replaces the Gallery app, and is installed separately through the NC store or you can use git to manage it as a submodule. If it doesn't work you might need to build its JavaScript resources by running {{code|make}} in its {{code|apps/photos}} directory. Alternatively you can resuscitate the Gallery app, which stopped working after NextCloud version 17, by updating its JavaScript dompurify library to a [https://raw.githubusercontent.com/nextcloud/gallery/ecb26a812f30a3f516964808ccfa6430be09b45e/js/vendor/dompurify/src/purify.js newer version] and bumping the app max version in {{code|apps/galleryappinfo/info.xml}} as follows (See also [https://github.com/nextcloud/gallery/pull/570/ pull request #570]):

<nextcloud min-version="17" max-version="'''20'''"/>

=== NextCloud Talk ===

'''''Note:''''' ''A TURN server can also be used for other things, like your own [https://matrix-org.github.io/synapse/v1.41/turn-howto.html Matrix] chat server.''

To be at all useful, you need a TURN server. This is easiest achieved by installing and correctly configuring coturn, which also acts as a STUN server. STUN is for clients to figure out their external IP address, and TURN is for relaying UDP packets, e.g. for WebRTC.

sudo apt install coturn

For it to actually do anything (it is disabled by default!) you need to edit {{code|/etc/default/coturn}} and make sure it contains the following, and is not commented out:

TURNSERVER_ENABLED=1

Then in {{code|/etc/turnserver.conf}} you need to configure its domain name, IP addresses and ports, SSL certificate, and how TURN authenticates. See GitHub for a fully documented example [https://github.com/coturn/coturn/blob/master/examples/etc/turnserver.conf turnserver.conf] file. This example uses the same domain as the NextCloud instance, on the standard STUN and TURN ports, but if you want to host it on port 443 to support people behind restrictive firewalls, you will need a different subdomain and/or IP address:

<syntaxhighlight lang="ini">
server-name=cloud.example.com
realm=cloud.example.com

# When hosted on an internal LAN or DMZ, it might look like this:
listening-ip=192.168.1.100
relay-ip=192.168.1.100
external-ip=123.45.67.8

# Or use the short-hand:
external-ip=123.45.67.8/192.168.1.100

# Specify listening ports; originally 3478 was unencrypted, and 5349 was TLS,
# but now TLS is supported on both. It is possible to listen on 443, which will
# help to provide connectivity for people behind port-limited firewalls; this will
# conflict with any HTTPS web hosting on the same server though, unless you set
# up a second IP address for it.
listening-port=3478
tls-listening-port=5349
#tls-listening-port=443

# Specify a UDP port range and ensure they are exposed through the firewall/router
min-port=53490
max-port=59999

# SSL setup;
cert=/path/to/cloud.example.com/fullchain.cert
pkey=/path/to/cloud.example.com/privkey.pem
no-tlsv1
no-tlsv1_1

# To use with NextCloud, set a shared secret here.
use-auth-secret
static-auth-secret=your-shared-secret-string-here
stale-nonce=600

# Limit STUN amplification/gain attacks (coturn 4.5.2+)
no-stun-backward-compatibility
response-origin-only-with-rfc5780

# RFC-5780 support requires two listening IP addresses and is for serious-cat
# usage, so you can safely disable it for a small home NextCloud instance.
no-rfc5780
</syntaxhighlight>

Note that the coturn user can't read certificates in <code>/etc/ssl/letsencrypt</code> so you will need a deploy hook script to copy them safely to somewhere it can read them:

<syntaxhighlight lang="sh">
#!/bin/sh
# /etc/letsencrypt/renewal-hooks/deploy/coturn
set -e
for domain in $RENEWED_DOMAINS; do
case $domain in
cloud.example.com)
certs=/etc/coturn/certs
fullchain="${certs}/${domain}_fullchain.pem"
key="${certs}/${domain}_privkey.pem"

# certificate and private key files are never world readable
umask 077
cp "${RENEWED_LINEAGE}/fullchain.pem" "$fullchain"
cp "${RENEWED_LINEAGE}/privkey.pem" "$key"

chown turnserver:turnserver "$fullchain" "$key"
chmod 400 "$fullchain" "$key"

systemctl restart coturn > /dev/null
;;
esac
done
</syntaxhighlight>

In your NextCloud Administration section, you can now configure Talk to use the STUN/TURN server, using your server URLs, and provide the same shared secret you configured above. For maximum compatibility, use both UDP and TCP, and both turn and turns (TLS) connections:

stun: '''cloud.example.com''':5349
turn and turns: '''cloud.example.com''':5349

Test your server [https://webrtc.github.io/samples/src/content/peerconnection/trickle-ice/ here]. If STUN is working, you should get {{code|srflx}} candidates, and {{code|relay}} candidates if TURN is working.

Sources and further reading:
* NextCloud Talk documentation: [https://nextcloud-talk.readthedocs.io/en/latest/TURN/ TURN], [https://nextcloud-talk.readthedocs.io/en/latest/coturn coturn]
* NextCloud Help, [https://help.nextcloud.com/t/howto-setup-nextcloud-talk-with-turn-server/30794 1] [https://help.nextcloud.com/t/nextcloud-talk-app-could-not-establish-a-connection-a-turn-server/131069/31 2]
* Stack Overflow [https://stackoverflow.com/questions/42801361/install-rfc5766-turn-server-trying-to-bind-fd-to-ip-address-errno-99 1]
* [https://ourcodeworld.com/articles/read/1526/how-to-test-online-whether-a-stun-turn-server-is-working-properly-or-not Testing STUN/TURN]
* Serverfault, question [https://serverfault.com/questions/849683/how-to-setup-coturn-with-letsencrypt #849683].

=== NextCloud Office ===

Install Collabora Office Online (COOL) which emerged from the smoking ruins of LibreOffice Online (LOOL):

apt install coolwsd code-brand

Use an Apache reverse proxy to handle the host and SSL, disable SSL in WOPI setup; restrict NextCloud and coolwsd requests to their respective endpoints, IP addresses; see [https://c-nergy.be/blog/?p=18055 "Install CODE on NextCloud on Ubuntu 22.04"]

== Maintenance ==

Keep the git checkout of NextCloud and its submodules up to date with git, and run the OCC upgrade command:

cd /var/www/nextcloud
git pull
git submodule update --init --recursive
sudo -u www-data ./occ upgrade

=== Cron job ===

In the administrator settings select cron rather than other methods, and put this in {{code|/etc/cron.d/nextcloud}}

*/5 * * * * www-data /usr/bin/php /var/www/nextcloud/cron.php

NextCloud

2025-09-16T23:33:50Z

Johnno: /* NextCloud Talk */ add LetsEncrypt cert fix

Assumes Ubuntu 20.04, stable20 branch, and installing in {{code|/var/www/nextcloud}} for a cloud.'''example.com''' URL (c. December 2020).

== Prerequisites ==

Install some stuff first. This NextCloud recipe uses [[PostgreSQL]], Redis, Apache 2.4, FastCGI with PHP FPM, and Dehydrated for [[Letsencrypt]] SSL certificate maangement.

sudo apt install apache2 libapache2-mod-fcgid \
php7.4-{bcmath,bz2,curl,gd,fpm,gmp,intl,json,mbstring,opcache,pgsql,tidy,xmlrpc,xsl,zip}
php-{imagick,redis} composer dehydrated postgresql redis-server

;NOTE: NextCloud works fine under nginx, I just can't be bothered maintaining what goes in {{code|.htaccess}} which NextCloud expects to be writeable to keep itself updated.

== Configuring Apache and PHP ==

: ''See the [[PHP]] page for setting up PHP with Apache, FPM and Event MPM.''

=== Add a virtual host for NextCloud ===

It should look something like this:

<syntaxhighlight lang="apache">
<VirtualHost *:443>
ServerName cloud.example.com
DocumentRoot /var/www/nextcloud

<Directory "/var/www/nexcloud">
Options FollowSymLinks MultiViews
AllowOverride All
Require all granted
Satisfy Any
SetEnv HOME /var/www/nextcloud
SetEnv HTTP_HOME /var/www/nextcloud

<IfModule mod_dav.c>
Dav off
</IfModule>
</Directory>

SSLEngine On
SSLCertificateFile ...
</VirtualHost>
</syntaxhighlight>

=== PHP settings ===

Enable the opcache, increase the memory limit, and increase the maximum POST and file upload size so you can upload large files.

== Create a database ==

Create a database in [[PostgreSQL]]:

sudo -u postgres createuser -SDRP ''<nextcloud-user>''
sudo -u postgres createdb -E UTF8 -O ''<nextcloud-user>'' ''<nextcloud-dbname>''

== Install NextCloud ==

We can just clone it from git:

git clone <nowiki>https://</nowiki>github.com/nextcloud/server.git nextcloud
cd nextcloud
git checkout stable20 ''# or whatever the latest stable version is''
git submodule update --init --recursive
chown www-data:www-data config apps .htaccess

Create a directory for the cache and user data, which should not reside under the web root:

mkdir -p /var/lib/nextcloud/data /var/cache/nextcloud
chown -R www-data:www-data /var/lib/nextcloud /var/cache/nextcloud

== Configure NextCloud ==

Edit the configuration file in {{code|config/config.php}} and check or consider these settings, and add the database and SMTP connection details:

<syntaxhighlight lang="php">
$config => [
# ...
#
'passwordsalt' => "<very long random string>",
'secret' => "<a different very long random string>",
'trusted_domains' => [
0 => "cloud.example.com",
],
'datadirectory' => '/var/lib/nextcloud/data',
'overwrite.cli.url' => '<nowiki>https://</nowiki>cloud.example.com/',
'htaccess.RewriteBase' => '/',

# Database connection details
'dbtype' => 'pgsql',
'dbhost' => 'localhost',
'dbuser' => "<nextcloud-dbuser>",
'dbpassword' => "<password>",
'dbname' => "<nextcloud-dbname>",
'dbtableprefix' => 'oc_',

# Force all URLs to use SSL or it may trip up things like OAuth
'overwriteprotocol' => 'https',

# Use Redis and caching to make everything go faster
'memcache.local' => '\\OC\\Memcache\\Redis',
'memcache.locking' => '\\OC\\Memcache\\Redis',
'redis' => [
'host' => 'localhost',
'port' => 6379,
],
'cache_path' => '/var/cache/nextcloud',

# Send email details
'mail_smtpmode' => 'smtp',
'mail_smtpsecure' => 'tls',
'mail_sendmailmode' => 'smtp',
'mail_from_address' => "nexcloud@example.com",
'mail_domain' => "example.com",
'mail_smtpauthtype' => 'PLAIN',
'mail_smtpauth' => 1,
'mail_smtphost' => "mail.example.com",
'mail_smtpport' => 587,
'mail_smtpname' => "<emailuser>",
'mail_smtppassword' => "<emailpassword>",

# ...
]
</syntaxhighlight>

Then navigate to the URL in the browser to install (or run the NextCloud {{code | ./occ}} tool from the command line).

;Note: Make sure that the directory used for {{code|cache_path}} exists and is writeable, or you will get weird seemingly unrelated 403 MKCOL errors to do with [https://docs.nextcloud.com/server/26/admin_manual/configuration_files/big_file_upload_configuration.html chunked uploads].

=== Clean URLs ===

For clean URLs, make sure {{code | AllowOverride All}} is set for the parent directory; the {{code | /var/www}} definition is in the main {{code | /etc/apache2/apache.conf}}. Then run the follwing OCC command to enable the rewrite rules:

sudo -u www-data ./occ maintenance:update:htaccess

=== Previews of other file types ===

Edit {{code|/etc/ImageMagick/policy.xml}} and remove this line to get PDF previews to work:

<syntaxhighlight lang="xml">
<policy domain="coder" rights="none" pattern="PDF" /> 
</syntaxhighlight>

Then add the following to {{code|config.php}} to get lots of other useful file previews:

<syntaxhighlight lang="php">
'enabledPreviewProviders' => [
# ...
8 => 'OC\\Preview\\MarkDown',
9 => 'OC\\Preview\\Movie',
10 => 'OC\\Preview\\PDF',
11 => 'OC\\Preview\\OpenDocument',
12 => 'OC\\Preview\\MSOfficeDoc',
13 => 'OC\\Preview\\MSOffice2003',
14 => 'OC\\Preview\\MSOffice2007',
],
</syntaxhighlight>

=== Photos ===

Photos replaces the Gallery app, and is installed separately through the NC store or you can use git to manage it as a submodule. If it doesn't work you might need to build its JavaScript resources by running {{code|make}} in its {{code|apps/photos}} directory. Alternatively you can resuscitate the Gallery app, which stopped working after NextCloud version 17, by updating its JavaScript dompurify library to a [https://raw.githubusercontent.com/nextcloud/gallery/ecb26a812f30a3f516964808ccfa6430be09b45e/js/vendor/dompurify/src/purify.js newer version] and bumping the app max version in {{code|apps/galleryappinfo/info.xml}} as follows (See also [https://github.com/nextcloud/gallery/pull/570/ pull request #570]):

<nextcloud min-version="17" max-version="'''20'''"/>

=== NextCloud Talk ===

'''''Note:''''' ''A TURN server can also be used for other things, like your own [https://matrix-org.github.io/synapse/v1.41/turn-howto.html Matrix] chat server.''

To be at all useful, you need a TURN server. This is easiest achieved by installing and correctly configuring coturn, which also acts as a STUN server. STUN is for clients to figure out their external IP address, and TURN is for relaying UDP packets, e.g. for WebRTC.

sudo apt install coturn

For it to actually do anything (it is disabled by default!) you need to edit {{code|/etc/default/coturn}} and make sure it contains the following, and is not commented out:

TURNSERVER_ENABLED=1

Then in {{code|/etc/turnserver.conf}} you need to configure its domain name, IP addresses and ports, SSL certificate, and how TURN authenticates. See GitHub for a fully documented example [https://github.com/coturn/coturn/blob/master/examples/etc/turnserver.conf turnserver.conf] file. This example uses the same domain as the NextCloud instance, on the standard STUN and TURN ports, but if you want to host it on port 443 to support people behind restrictive firewalls, you will need a different subdomain and/or IP address:

<syntaxhighlight lang="ini">
server-name=cloud.example.com
realm=cloud.example.com

# When hosted on an internal LAN or DMZ, it might look like this:
listening-ip=192.168.1.100
relay-ip=192.168.1.100
external-ip=123.45.67.8

# Or use the short-hand:
external-ip=123.45.67.8/192.168.1.100

# Specify listening ports; originally 3478 was unencrypted, and 5349 was TLS,
# but now TLS is supported on both. It is possible to listen on 443, which will
# help to provide connectivity for people behind port-limited firewalls; this will
# conflict with any HTTPS web hosting on the same server though, unless you set
# up a second IP address for it.
listening-port=3478
tls-listening-port=5349
#tls-listening-port=443

# Specify a UDP port range and ensure they are exposed through the firewall/router
min-port=53490
max-port=59999

# SSL setup;
cert=/path/to/cloud.example.com/fullchain.cert
pkey=/path/to/cloud.example.com/privkey.pem
no-tlsv1
no-tlsv1_1

# To use with NextCloud, set a shared secret here.
use-auth-secret
static-auth-secret=your-shared-secret-string-here
stale-nonce=600

# Limit STUN amplification/gain attacks (coturn 4.5.2+)
no-stun-backward-compatibility
response-origin-only-with-rfc5780

# RFC-5780 support requires two listening IP addresses and is for serious-cat
# usage, so you can safely disable it for a small home NextCloud instance.
no-rfc5780
</syntaxhighlight>

Note that the coturn user can't read certificates in <code>/etc/ssl/letsencrypt</code> so you will need a deploy hook script to copy them safely to somewhere it can read them:

<syntaxhighlight lang="sh">
#!/bin/sh
# /etc/letsencrypt/renewal-hooks/deploy/coturn
set -e
for domain in $RENEWED_DOMAINS; do
case $domain in
cloud.example.com)
certs=/etc/coturn/certs
fullchain="${certs}/${domain}_fullchain.pem"
key="${certs}/${domain}_privkey.pem"

# certificate and private key files are never world readable
umask 077
cp "${RENEWED_LINEAGE}/fullchain.pem" "$fullchain"
cp "${RENEWED_LINEAGE}/privkey.pem" "$key"

chown turnserver:turnserver "$fullchain" "$key"
chmod 400 "$fullchain" "$key"

service coturn restart > /dev/null
;;
esac
done
</syntaxhighlight>

In your NextCloud Administration section, you can now configure Talk to use the STUN/TURN server, using your server URLs, and provide the same shared secret you configured above. For maximum compatibility, use both UDP and TCP, and both turn and turns (TLS) connections:

stun: '''cloud.example.com''':5349
turn and turns: '''cloud.example.com''':5349

Test your server [https://webrtc.github.io/samples/src/content/peerconnection/trickle-ice/ here]. If STUN is working, you should get {{code|srflx}} candidates, and {{code|relay}} candidates if TURN is working.

Sources and further reading:
* NextCloud Talk documentation: [https://nextcloud-talk.readthedocs.io/en/latest/TURN/ TURN], [https://nextcloud-talk.readthedocs.io/en/latest/coturn coturn]
* NextCloud Help, [https://help.nextcloud.com/t/howto-setup-nextcloud-talk-with-turn-server/30794 1] [https://help.nextcloud.com/t/nextcloud-talk-app-could-not-establish-a-connection-a-turn-server/131069/31 2]
* Stack Overflow [https://stackoverflow.com/questions/42801361/install-rfc5766-turn-server-trying-to-bind-fd-to-ip-address-errno-99 1]
* [https://ourcodeworld.com/articles/read/1526/how-to-test-online-whether-a-stun-turn-server-is-working-properly-or-not Testing STUN/TURN]
* Serverfault, question [https://serverfault.com/questions/849683/how-to-setup-coturn-with-letsencrypt #849683].

=== NextCloud Office ===

Install Collabora Office Online (COOL) which emerged from the smoking ruins of LibreOffice Online (LOOL):

apt install coolwsd code-brand

Use an Apache reverse proxy to handle the host and SSL, disable SSL in WOPI setup; restrict NextCloud and coolwsd requests to their respective endpoints, IP addresses; see [https://c-nergy.be/blog/?p=18055 "Install CODE on NextCloud on Ubuntu 22.04"]

== Maintenance ==

Keep the git checkout of NextCloud and its submodules up to date with git, and run the OCC upgrade command:

cd /var/www/nextcloud
git pull
git submodule update --init --recursive
sudo -u www-data ./occ upgrade

=== Cron job ===

In the administrator settings select cron rather than other methods, and put this in {{code|/etc/cron.d/nextcloud}}

*/5 * * * * www-data /usr/bin/php /var/www/nextcloud/cron.php

PHP

2025-05-07T00:52:36Z

Johnno: /* Keep your PHP custom settings and tweaks separate */

: ''These instructions assume Ubuntu LTS (24.04, c. May 2025) but are easily adapted to Debian, a Docker or other container definition, Ansible playbook, etc.''

There's a lot of talk about using Nginx instead of Apache, and for some things it's probably easier to configure or maintain, especially if you're writing IaC, but some things still assume or prefer Apache, particularly in PHP land. [[WordPress]] and [[NextCloud]] are example PHP applications that want to modify their {{code|.htaccess}} files in-place, for example to keep up with security developments.

Perhaps part of the reason for the currently fashionable dim view of Apache's performance is that PHP is often run in production using mod-php, which ''at best'' should only be used on a development workstation, if at all. Apart from being incompatible with HTTP/2, it is a CPU and memory hog, since it can only run using Apache's horribly inefficient prefork multi-process management (MPM). Apache has several modules for dealing with MPM, but can only have one MPM module enabled at a time. Other much better, multithreaded, more efficient and fast MPM modules have been around for nearly two decades, and since version 5.4 of PHP (late 2011) we can use Apache's Event MPM and FastCGI modules with PHP-FPM. More information is available on the Apache [https://cwiki.apache.org/confluence/display/httpd/PHP-FPM httpd wiki].

With PHP processes being handled much more efficiently, and by being able to finally enable HTTP/2 in Apache, it all actually goes like the clappers.

== Prerequisites ==

Install at least the following packages:

sudo apt install apache2 libapache2-mod-fcgid php-fpm php8.3-{fpm,opcache}

You'll need various PHP extensions to host a real PHP application; for example [[NextCloud]] requires PHP composer, [[PostgreSQL]], Redis, and Dehydrated is useful for managing [[Letsencrypt]] SSL certificates.

sudo apt install php-{imagick,redis} \
php8.3-{bcmath,bz2,curl,gd,gmp,intl,json,mbstring,pgsql,soap,tidy,xmlrpc,xsl,zip} \
composer postgresql redis-server dehydrated

== Set up Apache ==

Enable the required Apache configurations and modules:

a2enconf php8.3-fpm
a2enmod dir env headers http2 mpm_event proxy_fcgi rewrite setenvif ssl
systemctl restart apache2

You should now be able to define your virtual hosts in {{code|/etc/apache2/sites-available}} and enable them, with PHP support.

=== Deny access to git repositories ===

Add a global config to deny access to git repositories and other hidden stuff, in {{code|/etc/apache2/conf-available/deny-git.conf}}:

''# /etc/apache2/conf-available/deny-git.conf''
<LocationMatch "/\.(git|hg|bzr|svn|cvs|tag|ht)">
# Old apache:
# Order deny,allow
# deny from all
Require all denied
</LocationMatch>

Then enable it with:

a2enconf deny-git
systemctl reload apache2

=== Add a virtual host ===

A virtual host for a typical PHP web application should look something like this:

<VirtualHost *:443>
ServerName app.'''example.com'''
DocumentRoot /var/www/app

<Directory "/var/www/app">
Options FollowSymLinks MultiViews
AllowOverride All
Require all granted
</Directory>

SSLEngine On
SSLCertificateFile ...
</VirtualHost>

See the [[Letsencrypt]] page for how to sort out your SSL certificates on pet servers.

== Configure PHP ==

The configuration for PHP code hosted by Apache using PHP-FPM is located under {{code|/etc/php/8.3/fpm}}.

You will not be able to use mod-php {{code|php_value}} directives in Apache configuration; either try and do without, or set environment variables instead.

PHP works a lot better with a few extra things configured. Enable the opcache, increase the memory limit, and depending on what you're hosting, increase the maximum POST size so users can upload photos and other large files. Other fun helpful things include using Redis for PHP sessions, and tuning the FPM process parameters to make stuff more efficient under load.

=== Keep your PHP custom settings and tweaks separate ===

Since Debian/Ubuntu now moves files around with each new version of PHP, you can keep your customisations to the {{code|php.ini}} file in a local configuration. For example, instead of editing {{code|/etc/php/8.3/fpm/php.ini}}, put these settings in {{code|/etc/php/local/php.ini}}:

; /etc/php/local/php.ini
; Override settings in the dist php.ini here
; make a symbolic link to this file in /etc/php/''<version>''/fpm/conf.d/99-local.ini
max_execution_time = 60
memory_limit = 512M
post_max_size = 500M
upload_max_filesize = 500M
opcache.enable=1

Then we add a symbolic link in the {{code|conf.d}} directory where it is needed, for example when new versions of PHP are released.

ln -s /etc/php/local/php.ini /etc/php/8.3/fpm/conf.d/99-local.ini

PHP

2025-05-07T00:38:03Z

Johnno: Update to 24.04 LTS and PHP 8.3

: ''These instructions assume Ubuntu LTS (24.04, c. May 2025) but are easily adapted to Debian, a Docker or other container definition, Ansible playbook, etc.''

There's a lot of talk about using Nginx instead of Apache, and for some things it's probably easier to configure or maintain, especially if you're writing IaC, but some things still assume or prefer Apache, particularly in PHP land. [[WordPress]] and [[NextCloud]] are example PHP applications that want to modify their {{code|.htaccess}} files in-place, for example to keep up with security developments.

Perhaps part of the reason for the currently fashionable dim view of Apache's performance is that PHP is often run in production using mod-php, which ''at best'' should only be used on a development workstation, if at all. Apart from being incompatible with HTTP/2, it is a CPU and memory hog, since it can only run using Apache's horribly inefficient prefork multi-process management (MPM). Apache has several modules for dealing with MPM, but can only have one MPM module enabled at a time. Other much better, multithreaded, more efficient and fast MPM modules have been around for nearly two decades, and since version 5.4 of PHP (late 2011) we can use Apache's Event MPM and FastCGI modules with PHP-FPM. More information is available on the Apache [https://cwiki.apache.org/confluence/display/httpd/PHP-FPM httpd wiki].

With PHP processes being handled much more efficiently, and by being able to finally enable HTTP/2 in Apache, it all actually goes like the clappers.

== Prerequisites ==

Install at least the following packages:

sudo apt install apache2 libapache2-mod-fcgid php-fpm php8.3-{fpm,opcache}

You'll need various PHP extensions to host a real PHP application; for example [[NextCloud]] requires PHP composer, [[PostgreSQL]], Redis, and Dehydrated is useful for managing [[Letsencrypt]] SSL certificates.

sudo apt install php-{imagick,redis} \
php8.3-{bcmath,bz2,curl,gd,gmp,intl,json,mbstring,pgsql,soap,tidy,xmlrpc,xsl,zip} \
composer postgresql redis-server dehydrated

== Set up Apache ==

Enable the required Apache configurations and modules:

a2enconf php8.3-fpm
a2enmod dir env headers http2 mpm_event proxy_fcgi rewrite setenvif ssl
systemctl restart apache2

You should now be able to define your virtual hosts in {{code|/etc/apache2/sites-available}} and enable them, with PHP support.

=== Deny access to git repositories ===

Add a global config to deny access to git repositories and other hidden stuff, in {{code|/etc/apache2/conf-available/deny-git.conf}}:

''# /etc/apache2/conf-available/deny-git.conf''
<LocationMatch "/\.(git|hg|bzr|svn|cvs|tag|ht)">
# Old apache:
# Order deny,allow
# deny from all
Require all denied
</LocationMatch>

Then enable it with:

a2enconf deny-git
systemctl reload apache2

=== Add a virtual host ===

A virtual host for a typical PHP web application should look something like this:

<VirtualHost *:443>
ServerName app.'''example.com'''
DocumentRoot /var/www/app

<Directory "/var/www/app">
Options FollowSymLinks MultiViews
AllowOverride All
Require all granted
</Directory>

SSLEngine On
SSLCertificateFile ...
</VirtualHost>

See the [[Letsencrypt]] page for how to sort out your SSL certificates on pet servers.

== Configure PHP ==

The configuration for PHP code hosted by Apache using PHP-FPM is located under {{code|/etc/php/8.3/fpm}}.

You will not be able to use mod-php {{code|php_value}} directives in Apache configuration; either try and do without, or set environment variables instead.

PHP works a lot better with a few extra things configured. Enable the opcache, increase the memory limit, and depending on what you're hosting, increase the maximum POST size so users can upload photos and other large files. Other fun helpful things include using Redis for PHP sessions, and tuning the FPM process parameters to make stuff more efficient under load.

=== Keep your PHP custom settings and tweaks separate ===

Since Debian/Ubuntu now moves files around with each new version of PHP, you can keep your customisations to the {{code|php.ini}} file in a local configuration. For example, instead of editing {{code|/etc/php/8.3/fpm/php.ini}}, put these settings in {{code|/etc/php/local/php.ini}}:

; /etc/php/local/php.ini
; Override settings in the dist php.ini here
; make a symbolic link to this file in /etc/php/''<version>''/fpm/conf.d/99-local.ini
max_execution_time = 60
memory_limit = 512M
post_max_size = 500M
upload_max_filesize = 500M
opcache.enable=1

Then we add a symbolic link in the {{code|conf.d}} directory where you need it, for example:

ln -s /etc/php/local/php.ini /etc/php/8.3/fpm/conf.d/99-local.ini

LXC

2025-03-17T20:29:40Z

Johnno: /* Set up a SSH shortcut for containers */

'''LXC''', or '''Linux Containers''', is a lightweight chroot-style virtual machine emulation in the main Linux kernel; most modern distributions should have it. The [https://help.ubuntu.com/serverguide/lxc.html LXC Ubuntu Server Guide] is very good and contains pretty much all you need to know. A lot of work has been done on 14.04 to make it very simple to get started and now supports unprivileged containers. These instructions assume Ubuntu 14.04 LTS.

== Install and set up LXC containers ==

In sum, we install LXC and create a base container, set it up nicely, then clone project containers from it as required. Here's roughly what I had to do to set up LXC nicely.

sudo apt-get install lxc

=== Optional: user configuration for unprivileged containers ===

You can use unprivileged containers, i.e. you don't need to use sudo all the time, and the containers live in your <code>~/.local/share/lxc</code> directory. The Ubuntu Server Guide tells you all you need to know to set this up; the short version is that unprivileged containers use user namespaces, so you need to create a subuid and subgid mapping for your user and allow your user to access the bridge network:

sudo usermod -v 100000-200000 -w 100000-200000 $USER
echo "$USER veth lxcbr0 2" | sudo tee -a /etc/lxc/lxc-usernet

Then configure your user account for LXC containers, :

mkdir -p ~/.config/lxc
echo "lxc.include = /etc/lxc/default.conf
lxc.idmap = u 0 100000 65536
lxc.idmap = g 0 100000 65536" > ~/.config/lxc/default.conf

=== Create a base container to serve as a template ===

The -d -r and -a options are distribution, release, and architecture.

lxc-create -t download -n base -- -d ubuntu -r trusty -a amd64
lxc-start -n base

Log in with the default account, (username ubuntu, password ''ubuntu''), then add yourself as a user with sudo:

sudo adduser '''username'''
sudo adduser '''username''' sudo

This installs some nifty default guff, fixes the probably broken UTC timezone, and fixes locales BEFORE you install things that expect UTF-8 locales, but in particular [[PostgreSQL]] (otherwise you end up with SQL_ASCII encoding and nobody wants that):

sudo apt-get install git bash-completion language-pack-en openssh-server
sudo dpkg-reconfigure tzdata

=== Set up a SSH shortcut for containers ===

Back on your host workstation, set up an SSH shortcut. If you add the following to your ~/.ssh/config, you will be able to ssh to your containers nice and easily:

# LXC containers
Host *.lxc
StrictHostKeyChecking no
UserKnownHostsFile /dev/null
ProxyCommand nc $(host -t A $(echo %h | sed "s/\.lxc//g") 10.0.3.1 | tail -1 | awk '{print $NF}') %p

Then one can ssh to the containers by appending .lxc to the container name:

ssh myproject.lxc

and then schlepp your handy stuff over:

scp -rp ~/.bashrc ~/.vim* ~/.gitconfig base.lxc:
scp ~/.ssh/authorized_keys base.lxc:.ssh/authorized_keys

=== Clone a new project container from the template ===

Now you should have a useful base container, so clone it for your ''nifty-kitten'' project and start it (most usefully in a screen session):

lxc-shutdown -n base
lxc-clone -o base -n '''nifty-kitten'''
screen
lxc-start -n '''nifty-kitten'''
'''''[Ctrl-a] [d]''''' ''to detach''

ssh nifty-kitten.lxc

Git

2025-03-03T00:24:59Z

Johnno: /* Installing Gitea */

== Handy git configuration ==

Put this in your ~/.gitconfig

[alias]
lol = log --graph --decorate --oneline
cdiff = diff --word-diff-regex=.
cshow = show --word-diff-regex=.
wdiff = diff --word-diff-regex=[^[:space:]]+
wshow = show --word-diff-regex=[^[:space:]]+ [color]
[color]
diff = auto
branch = auto
log = auto
status = auto
[push]
default = current
[user]
name = Harry Potter
email = harry@hogwarts.school.uk
[core]
autocrlf = input
pager = less -F -X

Bung these in your ~/.bashrc

export GIT_AUTHOR_NAME='Harry Potter'
export GIT_AUTHOR_EMAIL='harry@hogwarts.school.uk'
export GIT_COMMITTER_NAME=$GIT_AUTHOR_NAME
export GIT_COMMITTER_EMAIL=$GIT_AUTHOR_EMAIL
export GIT_PS1_SHOWDIRTYSTATE=1
export GIT_PS1_SHOWUNTRACKEDFILES=1
export GIT_PS1_SHOWUPSTREAM=auto

# very handy to have your git branch and checkout status in your prompt:
export PS1='\[\e[1m\]\u@\h:\w\[\e[32;1m\]$(__git_ps1 "(%s)")\[\e[m\e[1m\]\$\[\e[m\] '

== Track Mercurial transparently with git ==

To track a project hosted in a Mercurial repository, we can use the nifty hg-fast-export package. Install Mercurial and its fast export:

sudo apt-get install mercurial hg-fast-export

Use the Mercurial subcommand to clone your upstream Mercurial repository (Note the // in the path) into git.

git hg clone <nowiki>ssh://jack@beanstalk.net//var/lib/mercurial/magicbeans</nowiki>

Updating is easy:

cd ''magicbeans''
git hg fetch

== Importing a CVS project from SourceForge into git ==

First, grab a clone of the remote CVS repository. The easiest way to do this with a SourceForge project, without having to actually use CVS and its pserver logins and whatnot, is to use rsync:

rsync -avz rsync://meta-extractor.cvs.sourceforge.net/cvsroot/meta-extractor cvs-clone

Now we're going to import the history of the relevant CVS module (in this case, "metadata-extractor") into a new git repository.

sudo apt-get install git-cvsimport
git cvsimport -C meta-extractor.git -p x -v -d $(pwd)/cvs-clone metadata-extractor

Now you can push your new git project to Github or somewhere:

cd meta-extractor.git
git remote add origin <your-new-git-repo>
git push --tags master

You're good to go!

== Installing Forgejo ==

[https://forgejo.org/ Forgejo] is a [https://forgejo.org/2022-12-15-hello-forgejo/ community fork] of Gitea, which itself forked the quasi-abandoned Gogs project. It is not currently packaged, but it is a monolithic Go binary and therefore easy enough to stand up by following the [https://forgejo.org/docs/latest/admin/installation-binary/ binary install docs]. In sum, grab it (grab the .asc and verify with PGP):

wget -O /usr/local/bin/forgejo <nowiki>https://codeberg.org/forgejo/forgejo/releases/download/v10.0.1/forgejo-10.0.1-linux-amd64</nowiki>

Then install:

adduser --system --shell /bin/bash --gecos 'Git Version Control' --group --disabled-password --home /home/git git
mkdir -p /var/lib/forgejo/{custom,data,repositories} /var/log/forgejo /etc/forgejo
chown -R git:git /var/lib/forgejo
chown git:adm /var/log/forgejo
chown root:git /etc/forgejo
chmod -R 750 /var/lib/forgejo /var/log/forgejo /etc/forgejo

Edit <tt>/etc/systemd/system/forgejo.service</tt> using the [https://codeberg.org/forgejo/forgejo/src/branch/forgejo/contrib/systemd/forgejo.service example forgejo.service file] from the documentation as a guide. Set up an Apache or Nginx proxy definition:

<VirtualHost *:80>
ServerName git.'''mydomain.com'''
RewriteEngine On
RewriteCond %{HTTPS} off
RedirectMatch 301 ^(?!/\.well-known/acme-challenge/).* <nowiki>https://</nowiki>git.'''mydomain.com'''$0
Alias "/.well-known/acme-challenge/" "/var/www/acme-challenges/"
</VirtualHost>

<VirtualHost *:443>
SSLEngine On
SSLCertificateFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/cert.pem
SSLCertificateChainFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/chain.pem
SSLCertificateKeyFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/privkey.pem
Header always set Strict-Transport-Security "max-age=15768000"

ServerName git.'''mydomain.com'''
ServerAdmin webmaster@'''mydomain.com'''

ProxyRequests Off
ProxyPreserveHost On
ProxyPass / <nowiki>http://localhost:3000/</nowiki>
ProxyPassReverse / <nowiki>http://localhost:3000/</nowiki>

ErrorLog /var/log/apache2/forgejo/error.log
CustomLog /var/log/apache2/forgejo/access.log combined
</VirtualHost>

=== Updating Forgejo versions ===

sudo -u git forgejo --config /etc/forgejo/app.ini doctor check --all --log-file /tmp/doctor.log
# fix any errors
sudo -u git forgejo --config /etc/forgejo/app.ini manager flush-queues
wget <nowiki>https://codeberg.org/forgejo/.../forgejo-''latest''-linux-amd64</nowiki>
mv forgejo-''latest''-linux-amd64 /var/lib/forgejo/bin
chmod +x /var/lib/forgejo/bin/forgejo-''latest''-linux-amd64
systemctl stop forgejo.service
ln -sf /var/lib/forgejo/bin/forgejo-''latest''-linux-amd64 /usr/local/bin/forgejo
sudo -u git forgejo --config /etc/forgejo/app.ini migrate
sudo -u git forgejo --config /etc/forgejo/app.ini doctor check --all --log-file /tmp/doctor.log
# fix any errors
systemctl start forgejo.service

Git

2025-03-03T00:11:58Z

Johnno: /* Migrating to Forgejo */

== Handy git configuration ==

Put this in your ~/.gitconfig

[alias]
lol = log --graph --decorate --oneline
cdiff = diff --word-diff-regex=.
cshow = show --word-diff-regex=.
wdiff = diff --word-diff-regex=[^[:space:]]+
wshow = show --word-diff-regex=[^[:space:]]+ [color]
[color]
diff = auto
branch = auto
log = auto
status = auto
[push]
default = current
[user]
name = Harry Potter
email = harry@hogwarts.school.uk
[core]
autocrlf = input
pager = less -F -X

Bung these in your ~/.bashrc

export GIT_AUTHOR_NAME='Harry Potter'
export GIT_AUTHOR_EMAIL='harry@hogwarts.school.uk'
export GIT_COMMITTER_NAME=$GIT_AUTHOR_NAME
export GIT_COMMITTER_EMAIL=$GIT_AUTHOR_EMAIL
export GIT_PS1_SHOWDIRTYSTATE=1
export GIT_PS1_SHOWUNTRACKEDFILES=1
export GIT_PS1_SHOWUPSTREAM=auto

# very handy to have your git branch and checkout status in your prompt:
export PS1='\[\e[1m\]\u@\h:\w\[\e[32;1m\]$(__git_ps1 "(%s)")\[\e[m\e[1m\]\$\[\e[m\] '

== Track Mercurial transparently with git ==

To track a project hosted in a Mercurial repository, we can use the nifty hg-fast-export package. Install Mercurial and its fast export:

sudo apt-get install mercurial hg-fast-export

Use the Mercurial subcommand to clone your upstream Mercurial repository (Note the // in the path) into git.

git hg clone <nowiki>ssh://jack@beanstalk.net//var/lib/mercurial/magicbeans</nowiki>

Updating is easy:

cd ''magicbeans''
git hg fetch

== Importing a CVS project from SourceForge into git ==

First, grab a clone of the remote CVS repository. The easiest way to do this with a SourceForge project, without having to actually use CVS and its pserver logins and whatnot, is to use rsync:

rsync -avz rsync://meta-extractor.cvs.sourceforge.net/cvsroot/meta-extractor cvs-clone

Now we're going to import the history of the relevant CVS module (in this case, "metadata-extractor") into a new git repository.

sudo apt-get install git-cvsimport
git cvsimport -C meta-extractor.git -p x -v -d $(pwd)/cvs-clone metadata-extractor

Now you can push your new git project to Github or somewhere:

cd meta-extractor.git
git remote add origin <your-new-git-repo>
git push --tags master

You're good to go!

== Installing Gitea ==

[https://gitea.io/ Gitea] is an active fork of the quasi-abandoned Gogs project, and it is not currently packaged. It's a monolithic Go binary, so it's easy enough to stand up by following the [https://docs.gitea.io/en-us/install-from-binary/ binary install docs]. In sum, grab it (grab the .asc and verify with PGP):

wget -O /usr/local/bin/gitea <nowiki>https://dl.gitea.io/gitea/1.11.3/gitea-1.11.3-linux-amd64</nowiki>

Then install:

adduser --system --shell /bin/bash --gecos 'Git Version Control' --group --disabled-password --home /home/git git
mkdir -p /var/lib/gitea/{custom,data,repositories} /var/log/gitea /etc/gitea
chown -R git:git /var/lib/gitea
chown git:adm /var/log/gitea
chown root:git /etc/gitea
chmod -R 750 /var/lib/gitea /var/log/gitea /etc/gitea

Edit <tt>/etc/systemd/system/gitea.service</tt> using the [https://github.com/go-gitea/gitea/blob/master/contrib/systemd/gitea.service example gitea.service file] from the documentation as a guide. Set up an Apache or Nginx proxy definition:

<VirtualHost *:80>
ServerName git.'''mydomain.com'''
RewriteEngine On
RewriteCond %{HTTPS} off
RedirectMatch 301 ^(?!/\.well-known/acme-challenge/).* <nowiki>https://</nowiki>git.'''mydomain.com'''$0
Alias "/.well-known/acme-challenge/" "/var/www/acme-challenges/"
</VirtualHost>

<VirtualHost *:443>
SSLEngine On
SSLCertificateFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/cert.pem
SSLCertificateChainFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/chain.pem
SSLCertificateKeyFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/privkey.pem
Header always set Strict-Transport-Security "max-age=15768000"

ServerName git.'''mydomain.com'''
ServerAdmin webmaster@'''mydomain.com'''

ProxyRequests Off
ProxyPreserveHost On
ProxyPass / <nowiki>http://localhost:3000/</nowiki>
ProxyPassReverse / <nowiki>http://localhost:3000/</nowiki>

ErrorLog /var/log/apache2/gitea/error.log
CustomLog /var/log/apache2/gitea/access.log combined
</VirtualHost>

=== Migrating to Forgejo ===

...and here we go again.

sudo -u git forgejo --config /etc/forgejo/app.ini doctor check --all --log-file /tmp/doctor.log
# fix any errors
sudo -u git forgejo --config /etc/forgejo/app.ini manager flush-queues
wget <nowiki>https://codeberg.org/forgejo/.../forgejo-''latest''-linux-amd64</nowiki>
mv forgejo-''latest''-linux-amd64 /var/lib/forgejo/bin
chmod +x /var/lib/forgejo/bin/forgejo-''latest''-linux-amd64
systemctl stop forgejo.service
ln -sf /var/lib/forgejo/bin/forgejo-''latest''-linux-amd64 /usr/local/bin/forgejo
sudo -u git forgejo --config /etc/forgejo/app.ini migrate
sudo -u git forgejo --config /etc/forgejo/app.ini doctor check --all --log-file /tmp/doctor.log
# fix any errors
systemctl start forgejo.service

Git

2025-03-03T00:10:48Z

Johnno: /* Installing Gitea */

== Handy git configuration ==

Put this in your ~/.gitconfig

[alias]
lol = log --graph --decorate --oneline
cdiff = diff --word-diff-regex=.
cshow = show --word-diff-regex=.
wdiff = diff --word-diff-regex=[^[:space:]]+
wshow = show --word-diff-regex=[^[:space:]]+ [color]
[color]
diff = auto
branch = auto
log = auto
status = auto
[push]
default = current
[user]
name = Harry Potter
email = harry@hogwarts.school.uk
[core]
autocrlf = input
pager = less -F -X

Bung these in your ~/.bashrc

export GIT_AUTHOR_NAME='Harry Potter'
export GIT_AUTHOR_EMAIL='harry@hogwarts.school.uk'
export GIT_COMMITTER_NAME=$GIT_AUTHOR_NAME
export GIT_COMMITTER_EMAIL=$GIT_AUTHOR_EMAIL
export GIT_PS1_SHOWDIRTYSTATE=1
export GIT_PS1_SHOWUNTRACKEDFILES=1
export GIT_PS1_SHOWUPSTREAM=auto

# very handy to have your git branch and checkout status in your prompt:
export PS1='\[\e[1m\]\u@\h:\w\[\e[32;1m\]$(__git_ps1 "(%s)")\[\e[m\e[1m\]\$\[\e[m\] '

== Track Mercurial transparently with git ==

To track a project hosted in a Mercurial repository, we can use the nifty hg-fast-export package. Install Mercurial and its fast export:

sudo apt-get install mercurial hg-fast-export

Use the Mercurial subcommand to clone your upstream Mercurial repository (Note the // in the path) into git.

git hg clone <nowiki>ssh://jack@beanstalk.net//var/lib/mercurial/magicbeans</nowiki>

Updating is easy:

cd ''magicbeans''
git hg fetch

== Importing a CVS project from SourceForge into git ==

First, grab a clone of the remote CVS repository. The easiest way to do this with a SourceForge project, without having to actually use CVS and its pserver logins and whatnot, is to use rsync:

rsync -avz rsync://meta-extractor.cvs.sourceforge.net/cvsroot/meta-extractor cvs-clone

Now we're going to import the history of the relevant CVS module (in this case, "metadata-extractor") into a new git repository.

sudo apt-get install git-cvsimport
git cvsimport -C meta-extractor.git -p x -v -d $(pwd)/cvs-clone metadata-extractor

Now you can push your new git project to Github or somewhere:

cd meta-extractor.git
git remote add origin <your-new-git-repo>
git push --tags master

You're good to go!

== Installing Gitea ==

[https://gitea.io/ Gitea] is an active fork of the quasi-abandoned Gogs project, and it is not currently packaged. It's a monolithic Go binary, so it's easy enough to stand up by following the [https://docs.gitea.io/en-us/install-from-binary/ binary install docs]. In sum, grab it (grab the .asc and verify with PGP):

wget -O /usr/local/bin/gitea <nowiki>https://dl.gitea.io/gitea/1.11.3/gitea-1.11.3-linux-amd64</nowiki>

Then install:

adduser --system --shell /bin/bash --gecos 'Git Version Control' --group --disabled-password --home /home/git git
mkdir -p /var/lib/gitea/{custom,data,repositories} /var/log/gitea /etc/gitea
chown -R git:git /var/lib/gitea
chown git:adm /var/log/gitea
chown root:git /etc/gitea
chmod -R 750 /var/lib/gitea /var/log/gitea /etc/gitea

Edit <tt>/etc/systemd/system/gitea.service</tt> using the [https://github.com/go-gitea/gitea/blob/master/contrib/systemd/gitea.service example gitea.service file] from the documentation as a guide. Set up an Apache or Nginx proxy definition:

<VirtualHost *:80>
ServerName git.'''mydomain.com'''
RewriteEngine On
RewriteCond %{HTTPS} off
RedirectMatch 301 ^(?!/\.well-known/acme-challenge/).* <nowiki>https://</nowiki>git.'''mydomain.com'''$0
Alias "/.well-known/acme-challenge/" "/var/www/acme-challenges/"
</VirtualHost>

<VirtualHost *:443>
SSLEngine On
SSLCertificateFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/cert.pem
SSLCertificateChainFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/chain.pem
SSLCertificateKeyFile /etc/ssl/letsencrypt/git.'''mydomain.com'''/privkey.pem
Header always set Strict-Transport-Security "max-age=15768000"

ServerName git.'''mydomain.com'''
ServerAdmin webmaster@'''mydomain.com'''

ProxyRequests Off
ProxyPreserveHost On
ProxyPass / <nowiki>http://localhost:3000/</nowiki>
ProxyPassReverse / <nowiki>http://localhost:3000/</nowiki>

ErrorLog /var/log/apache2/gitea/error.log
CustomLog /var/log/apache2/gitea/access.log combined
</VirtualHost>

=== Migrating to Forgejo ===

...and here we go again.

sudo -u git forgejo --config /etc/forgejo/app.ini doctor check --all --log-file /tmp/doctor.log
# fix any errors
sudo -u git forgejo --config /etc/forgejo/app.ini manager flush-queues
wget <nowiki>https://codeberg.org/forgejo/.../forgejo-''latest''-linux-amd64</nowiki>
mv forgejo-''latest''-linux-amd64 /var/lib/forgejo/bin
chmod +x /var/lib/forgejo/bin/forgejo-''latest''-linux-amd64
sudo systemctl stop forgejo.service
ln -sf /var/lib/forgejo/bin/forgejo-''latest''-linux-amd64 /usr/local/bin/forgejo
sudo -u git forgejo --config /etc/forgejo/app.ini migrate
sudo -u git forgejo --config /etc/forgejo/app.ini doctor check --all --log-file /tmp/doctor.log
# fix any errors
sudo systemctl start forgejo.service

Hacks

2024-12-16T21:10:29Z

Johnno: /* Fancy PostgreSQL prompts */

Hacks

2024-12-16T21:10:12Z

Johnno:

== Image renaming by create date ==

When you have a bunch of files from your old Android phone with a timestamp filename (e.g. 1388042328550.jpg), you can rename them thus:

for f in 1*.jpg; do
mv $f $(date +IMG_%Y%m%d_%H%M%S.jpg -d @$(echo $f|cut -c 1-10))
done

Otherwise, we can fish out the Create Date from the EXIF data, using exiftool:

exiftool '-FileName<CreateDate' -d %Y%m%d_%H%M%S%%-c.%%e .

== Use etckeeper ==

It saves your bacon. Store changes to the {{path|/etc}} directory in git, uses hooks to commit changes when packages are updated, puppet runs, etc. Stash your own changes with useful commit messages. Look up stuff in history when something goes wrong, figure out what changes happened when packages are added or updated.

=== Fix the daily commit bug ===

Unfortunately since about 18.04 there is a bug where a systemd timer and a cron.daily task compete to commit a daily commit if needed; unfortunately one of them doesn't bother to check the {{path|etckeeper.conf}} file to see whether you've disabled it. If you have <code>DISABLE_DAILY_AUTOCOMMITS=1</code> in your config file, then you probably don't want daily auto commits. If this is so, run this to disable the systemd timer:

systemctl disable --now etckeeper.timer

== Repair broken or rotated phone videos ==

You can fix broken video .tmp files from your Android phone using [https://github.com/ponchio/untrunc/ untrunc]. This will create a fixed.mp4 file in cwd:

untrunc working_video_from_the_same_camera.mp4 broken.mk4.tmp

Did your phone unhelpfully rotate your video? Fix the metadata:

ffmpeg -i original.mp4 -acodec copy -vcodec copy -metadata:s:v:0 rotate=0 unrotated.mp4

Or, did you actually film your shit the wrong way up? You'll need to re-encode it:

ffmpeg -i original.mp4 -acodec copy -vcodec libx264 -crf 20 -metadata:s:v:0 rotate=0 unrotated.mp4

== Export your photos ==

Before uploading your photos, remove all the EXIF tags, and reduce the resolution; nobody needs 5000x4000 pixels, much less corporations hoovering up your stuff to put in adverts.

mkdir tmp
for f in $.jpg; do
convert -resize 1600x1200 $f tmp/$f
exiftool -all= -P -overwrite_original tmp/$f
done

== Clean out your revoked and expired PGP keys ==

Spring-clean your gpg database, make it go faster. Just go:

gpg --delete-keys $(gpg --list-public-keys|grep -PB1 'expired|revoked'|grep '^\s')

Obviously. If you can't be arsed confirming each key, add <tt>--batch --yes</tt> to the first gpg call.

== Firefox opening your shit in gedit ==

I don't know, who thought that was a good idea? Jesus.

sed -i 's#.*octet-stream.*#application/octet-stream=xdg-open#' ~/.local/share/applications/mimeapps.list ~/.config/mimeapps.list

== GNOME not sorting your folders first ==

It's not in a dialog anywhere, and a pretty lame default behaviour.
dconf write /org/gnome/nautilus/preferences/sort-directories-first true

== GNOME not dealing with WebP images ==

I mean, it's 2021 and WebP is only what, ten years old? Jesus. See solution on [https://askubuntu.com/a/617068 AskUbuntu].

== Restoring huge databases to PostgreSQL ==

'''''See also:''''' ''[[PostgreSQL]]''

Skip some huge tables by dumping the TOC, removing culprit large tables, and restore with the edited TOC, e.g.

pg_restore -l huge.dump > TOC
# edit the TOC file...
pg_restore -L TOC -d dbname huge.dump

== Output PostgreSQL queries to CSV ==

COPY (SELECT <query> ...) TO STDOUT CSV HEADER;

== Turn off the shitty default "visual" mouse mode in vim ==

Add this to <code>/etc/vim/vimrc.local</code> file:<ref>Böhnke, H (16 March 2019). [https://unix.stackexchange.com/a/506723 Answer 506723] on "Disabling mouse support in vim in a gnome-terminal environment", Unix & Linux Stack Exchange.</ref>

" Use skip_defaults_vim from the master /etc/vim/vimrc file
if ! exists('skip_defaults_vim')
" Source the defaults file manually from here
source $VIMRUNTIME/defaults.vim
endif

" Avoid loading the defaults twice
let g:skip_defaults_vim = 1

" Revert any unwanted changes the defaults file introduced
set mouse=

== Too many leftover git branches merged into master? Nukem! ==

This will remove any branches on your local git repo that have been merged to master.

for i in $(for b in $(git branch|grep -v '\*'); do git branch $b --merged master; done); \
do git branch -D $i; \
done

== Move your MySQL databases ==

Used to be you could move your /var/lib/mysql directory somewhere else and symlink it. You can, but in newer distros with Apparmor you'll need to declare it:

# In /etc/apparmor.d/tunables/alias
alias /var/lib/mysql/ -> /mnt/wherever/mysql/,

Note the trailing comma.

== Split audio CD rips using the cue file ==

Single-file FLAC or Ape files of a CD can be split up using the .cue file for both the timestamps and the tagging data. Also, install flacon (from a PPA).

sudo apt install shntool
shnsplit -o flac cdimage.flac -f cdimage.cue -t '%n.%t'

You may need to do tags again, if they didn't stick:

sudo apt install cuetools
cuetag cdimage.cue *.flac

== Ditch Snap ==

=== 1. Install Firefox properly ===

Ubuntu 22.04 replaces the Firefox Debian package with a pseudo-package that installs it using Snap. This is no longer optional, and it is also stupid, annoying, slow, and potentially insecure for many reasons.<ref>Brandon Hopkins. [https://techhut.tv/flatpak-vs-snap-vs-appimage/ "Flatpak vs. Snap vs. AppImage - Linux Packaging Benchmarks"] TechHut, April 2022.</ref> Several Firefox extensions that need to talk to other applications don't currently work in Snap installed Firefox, including things like Zotero and Keepass.

It also completely fails to find and import any existing Firefox profile. Snap apps for whatever stupid reason keep settings in a hard-coded ~/snap folder. Who thought that was a good idea? What's wrong with ~/.config and/or ~/.local, or couldn't they even use ~/.snap? Good grief.

Luckily, we can use the Mozilla Team PPA instead.<ref>Ji M. [https://ubuntuhandbook.org/index.php/2022/04/install-firefox-deb-ubuntu-22-04/ "How to Install Latest Firefox as classic Deb in Ubuntu 22.04"] Ubuntu Handbook, April 2022.</ref> In summary:
<syntaxhighlight lang="bash">
# Remove Firefox entirely:
snap remove firefox
apt remove firefox

# Add the Mozilla Team PPA and install:
add-apt-repository ppa:mozillateam/ppa
apt update
apt install -t 'o=LP-PPA-mozillateam' firefox

# Pin to the Mozilla Team PPA so we never get the Snap pseudo-package
echo 'Package: firefox*
Pin: release o=LP-PPA-mozillateam
Pin-Priority: 501' > /etc/apt/preferences.d/mozillateam-ppa.pref
</syntaxhighlight>

=== 2. Remove snap ===

You can also remove snap entirely as follows:

<syntaxhighlight lang="bash">
# get a list of things installed with snap:
snap list

# remove them one by one:
snap uninstall ''program1''
snap uninstall ''program2''

# once there's nothing left, remove snap:
apt purge snapd

# then prevent it ever being re-installed with a pin preference:
echo 'Package: snapd
Pin: release a=*
Pin-Priority: -10' > /etc/apt/preferences.d/no-snap.pref
</syntaxhighlight>

==Fancy PostgreSQL prompts==

For a coloured prompt including what user, connection, and host you're on, stick this in ~/.psqlrc or /etc/postgresql-common/psqlrc:

\set PROMPT1 '%[%033[1m%](%n@%[%033[1;35m%]%M%[%033[m%]%[%033[1m%] on %[%033[1;31m%]%`hostname`%[%033[m%]%[%033[1m%]) \n%/%R%#%[%033[m%] '
\set PROMPT2 '%[%033[1m%]%/%R%#%[%033[m%] '
\set PROMPT3 '%[%033[1m%]>>%[%033[m%] '

Escape codes use '%' character, documented here: [https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PROMPTING Psql Prompts].

==Using rsync with FAT volumes (USB sticks and SD cards)

You might have noticed that all your files have today's timestamp on them when using rsync thus:
rsync -av /path/to/my/stuff /mnt/usb-stick/

The -a option means -rlDtpog, which is recursive, include links and devices, preserving timestamps, permissions, owner and group. Which is what you want most of the time. Trouble is, FAT partitions have no concept of permissions, owners and groups, so you need to use:
rsync -rtlDv /path/to/my/stuff /mnt/usb-stick/

But that's probably not all, because rsync will probably complain that it can't set the timestamp. You'll need to mount your USB stick so it ignores a user check that FAT can't enforce:
mount -o remount,allow_utime=2 /mnt/usb-stick

==Notes==

<references/>

PostgreSQL

2024-09-19T00:24:17Z

Johnno: /* Troubleshooting */

== Documentation ==

The online [http://www.postgresql.org/docs/current/ PostgreSQL Manual] is actually quite awesome and worth bookmarking.

== Install and Configure ==

Assuming Debian or Ubuntu, installation is:
sudo apt-get install postgresql postgresql-client

=== Check The Configuration ===

You may end up with (and may require) several different versions of PostgreSQL on one box. If this is so, check that they are running on separate ports. The port is specified in <tt>/etc/postgresql/9.1/main/postgresql.conf</tt>. For a development ('''NOT''' production!) environment, we can make life simple by making a change to <tt>/etc/postgresql/9.1/main/pg_hba.conf</tt>:

local all all trust
host all all 127.0.0.1/32 trust

That is, changing the authentication from ''md5'' to ''trust'' for local socket-based connections and TCP connections from localhost. On production servers, <tt>pg_hba.conf</tt> provides the means for a powerful and atomic user- and host-based authentication system to really lock the whole thing down nicely.

;NOTE: If you have changed the port or anything else in <tt>postgresql.conf</tt>, you will need to stop then start the service, as reload and restart are '''not''' sufficient! This is a nasty trap for young players.

== Creating Users and Databases ==

PostgreSQL system administration is somewhat different in approach to MySQL, but actually much less retarded once you get the hang of it.

With PostgreSQL creating users and databases is done with shell level commands <tt>createuser</tt> and <tt>createdb</tt>, rather than lame non-standard SQL commands (although you can also use SQL-ish statements like MySQL if you insist, but they are not covered here).

=== Make Yourself A Superuser ===

Out of the box, the PostgreSQL server runs as the postgres user, so to create yourself an administrator account, you need to run the createuser script as the postgres user:

sudo -u postgres createuser <yourusername>

Use your shell account name and answer ''Y'' to all questions to give yourself a superuser account. Unless told otherwise, PostgreSQL will try to use the shell account to save time. If you don't want question prompts, you can go

sudo -u postgres createuser -srdP <username>

;NOTE: Reload the PostgreSQL server to pick up the new privileges.

==== Switches for createuser ====

{| border=1
|-
| '''Switch''' || '''Behaviour'''
|-
| ''-s'' || User will be a superuser
|-
| ''-S'' || User will not be a superuser
|-
| ''-r'' || User can create other roles (users)
|-
| ''-R'' || User cannot create other roles (users)
|-
| ''-d'' || User can create databases
|-
| ''-D'' || User cannot create databases
|-
| ''-P'' || Prompt to create a password for the user
|}

==== Common Switches For All Commands ====
{| border=1
|-
| '''Switch''' || '''Behaviour'''
|-
| ''-h'' || Host - hostname of the PostgreSQL server ''(defaults to localhost)''
|-
| ''-p'' || Port - PostgreSQL server port ''(defaults to 5432)''
|-
| ''-U'' || User - specify the user to connect as ''(defaults to the shell user)''
|-
| ''-W'' || Force PostgreSQL to ask you for a password before connecting
|}

=== Create Non-Privileged Users ===

Usually, you give yourself admin rights, and then get your applications to use non-privileged accounts. For production boxes, that's usually one account per different application and/or database, but for a development box we can often just use a single dev account:

createuser -SDRP devuser

=== Drop Users ===

You can also drop a user with the <tt>dropuser</tt> shell command:

dropuser <username>

=== Create A Database ===

Now we can create a database. Being 2008, we like to use UTF8 so we need to specify that, since the default is still ASCII.

createdb -O devuser -E UTF8 <databasename>

==== Switches for createdb ====
{| border=1
|-
| '''Switch''' || '''Behaviour'''
|-
| ''-O'' || Owner - the user that owns the database
|-
| ''-E'' || Encoding - almost always UTF8
|}

=== Drop A Database ===

You can also drop a database with the <tt>dropdb</tt> shell command:

dropdb <databasename>

== Using The Client ==

The cli client is <tt>psql</tt>. To get a list of databases on the system, you can use the ''-l'' switch:

psql -l

=== Connect To A Database ===

If your database has been created, you should be able to use the PostgreSQL client:

psql <databasename> [<username>]

Since you are a superuser, you should be able to get amongst it without having to specify the username.

=== Backslash Commands ===

;TODO: once at the psql prompt, type \? for help with the various backslash commands, and \h for help with SQL syntax.

{| border=1
|-
| '''Command''' || '''Behaviour'''
|-
| ''\dt'' || List the tables
|-
| ''\d <tablename>'' || Show table schema
|-
| ''\x'' || Toggle to horizontal format when displaying rows
|-
| ''\i <filename>'' || Execute a bunch of SQL in the file
|-
| ''\cd <path>'' || Change directory (useful for \i)
|-
| ''\l'' || List all databases
|-
| ''\c <database>'' || Connect to another database
|-
| ''\q'' || Quit
|}

== Backing Up And Restoring Databases ==

We use the <tt>pg_dump</tt> and <tt>pg_restore</tt> shell commands. Ownership of the database objects is probably the most common source of confusion here. Since most of the time ownership will not be more atomic than database-level, it is usually easiest to avoid the whole issue by not including ownership data in the dumps. It can then be safely determined by specifying the owner during the restore. This also means you can change the ownership if necessary, without unnecessary bloodshed.

=== Backup ===

To create a compressed format (-Fc) dump file (-f), that does not specify object ownerships (-O), execute the following:

pg_dump -Fc -O -f <dumpfile> <databasename>

=== Restore ===

During a restore, the ownership is set to whichever user the restore command connects to the database as. So to restore a dump file into an existing database (-d), without using ownerships from the file (-O), but specifying the connecting user (-U) as the owner:

pg_restore -O -U <username> -d <databasename> <dumpfile>

;NOTE: You usually want the database to be empty, having (re)created a new one for the restore.

== Troubleshooting ==

;I can't drop the database!
:You may need to stop Apache before you can drop a database, otherwise PostgreSQL will complain that it is in use.
:Or, you can create a ''/usr/local/bin/pg_kill_connections'' script:

:<syntaxhighlight lang="bash">
#!/bin/sh
DATABASE="$1"

if [ -z "${DATABASE}" ];then
echo "Usage: pg_kill_connections dbname"
exit 1;
fi

for i in $(su postgres -c \
"psql -qt -c \"\\timing false\" \
-c \"SELECT pid FROM pg_stat_activity WHERE datname='${DATABASE}'\" \
template1 | xargs -r kill"); do
kill $i
done
</syntaxhighlight>

;How do I rename a database?
:ALTER DATABASE "foo" RENAME TO "bar";

;How do I concatenate strings?
:SELECT 'Hello' || ' ' || 'World';

== See Also ==
* [[PostgreSQL/From_MySQL]]
* [[PostgreSQL full text searching]]

2022-11-30T05:56:40Z

Johnno:

;Note 1: This page is the SVG output version of [[Score examples]], using a hacked version of the Score extension; see [https://phabricator.wikimedia.org/T49578 T49578].

;Note 2: To yoink Lilypond snippets from Wikipedia articles, see [https://en.wikipedia.org/wiki/Special:PagesWithProp?propname=score&namespace=0 Special:PagesWithProp] and search for the "score" property in Article space.

From [https://en.wikipedia.org/wiki/Cadence Cadence]:

[[File:ru47w83v.svg]]

== Multi-system input ==

From [https://en.wikipedia.org/wiki/String_Quartet_No._1_(Górecki) String Quartet No. 1 (Górecki)]

[[File:0og4sm0i.svg]]

== Testing raw input ==

And some [https://en.wikipedia.org/wiki/Keyboard_Sonata,_K._141_(Scarlatti) Keyboard Sonata, K. 141 (Scarlatti)], testing raw=1:

[[File:7hc4jfka.svg]]

File:7hc4jfka.svg

2022-11-30T05:55:22Z

Johnno:

File:0og4sm0i.svg

2022-11-30T05:46:59Z

Johnno: ZZ

== Summary ==
ZZ
== Licensing ==
{{self|cc-zero}}

File:Ru47w83v.svg

2022-11-30T05:26:31Z

Johnno: NA

== Summary ==
NA
== Licensing ==
{{self|cc-zero}}