7,612
edits
Changes
Jump to navigation
Jump to search
=====PostGIS Issues=====
Research Computing Configuration (view source)
Revision as of 15:39, 28 October 2020
, 15:39, 28 October 2020→The Front Page
To do the front page, I copied the source of [https://www.mediawiki.org/w/index.php?title=Template:Main_page&action=edit Template:Main page] from mediawiki to a page (called Test) and created [Template:Main page/styles.css] using [https://www.mediawiki.org/w/index.php?title=Template:Main_page/styles.css&action=edit mediawiki's code]. Then I rejigged the contents of the page!
The only minor but non-obvious change, was that I used h2 headings inside each mainpage_box, rather than h3's. As a consequence, I needed to add the following to [[Template:Main_page/styles.css]]:
.mainpage_box h2 {
border-bottom: none;
}
.mainpage_box h2 .header_icon {
margin-right: 5px;
}
Old instructions[https://www.mediawiki.org/wiki/Topic:R1j08xhjgrtkpj6q] suggest using [[Special:ExpandTemplate]] on mediawiki's wiki, with the input text <nowiki>{{:MediaWiki}}</nowiki>. But this isn't necessary as the template doesn't need expanding in its current (at the time of writing) incarnation. Naturally, the page works well on MobileFrontend.
And the wiki now seems happy!
I also checked postgres and everything seemed ok:
su researcher
\dx
All my extensions report back.
Update: It seems something did go wrong. Just because the extensions report back doesn't mean they work! When I try to run queries that use PostGIS, I get:
SQL Error [58P01]: ERROR: could not access file "$libdir/postgis-2.4": No such file or directory
I tried updating the extension (I'm pretty sure that I'm running 2.4.3):
ALTER EXTENSION postgis UPDATE TO "2.4.3";
But that didn't fix anything. I checked the versions:
select version();
PostgreSQL 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
--So somehow I'm still running version 10!
select PostGIS_full_version();
ERROR: could not access file "$libdir/postgis-2.4": No such file or directory
CONTEXT: SQL statement "SELECT public.postgis_lib_version()"
PL/pgSQL function postgis_full_version() line 25 at SQL statement
So I backed off the data from the two dbases that I'd used since the upgrade:
pg_dump -Fc stockmarket > stockmarket_Fc_20201023.dump
pg_dump -Fc vcdb4 > vcdb4_Fc_20201023.dump
#FAILED!
The second backoff failed:
pg_dump: [archiver (db)] query failed: ERROR: could not access file "$libdir/postgis-2.4": No such file or directory
pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef,
a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions,
CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, a.attidentity, pg_catalog.array_to_string(ARRAY(SELECT
pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name),
E',
') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '19998614'::pg_catalog.oid AND
a.attnum > 0::pg_catalog.int2 ORDER BY a.attnum
======Postgres Upgrade Attempt (Failed)======
My changes weren't substantial, so I proceeded with an upgrade. First I checked to see if I had postgres12 installed and listening on another port or not:
locate postgres
ls /usr/bin/postgres
dpkg --get-selections | grep postgres
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /data/postgres /var/log/postgresql/postgresql-10-main.log
12 main 5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
pg_upgradecluster 10 main
#This failed:
pg_dump: error: query failed: ERROR: could not access file "$libdir/postgis-2.4": No such file or directory
So... I can't automatically upgrade without first fixing the issue with v10 and postgis.
add-apt-repository http://apt.postgresql.org/pub/repos/apt
But that put the following into /etc/apt/sources-list:
deb http://apt.postgresql.org/pub/repos/apt focal main
vi it to (see https://wiki.postgresql.org/wiki/Apt):
deb http://apt.postgresql.org/pub/repos/apt focal-pgdg
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
apt-get update
#Throws a warning:
N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
But the wretched thing still doesn't seem to be available:
apt-get install postgresql-10-postgis-2.4
Package postgresql-10-postgis-2.4 is not available, but is referred to by another package.
Trying a manual approach. [https://packages.ubuntu.com/bionic/i386/postgresql-10-postgis-2.4/download Get the file], put it in /bulk/temp and cd there. Then:
apt-get install ./postgresql-10-postgis-2.4_2.4.3+dfsg-4_i386.deb
#This failed too - there are unmet dependencies and they are 'not installable'.
======Switching over the installations======
So, I took the alternative approach of changing the data folders [https://www.tutorialspoint.com/how-to-change-postgresql-data-folder-location-on-ubuntu-16-04].
The plan:
#Take version 10 offline
#Move version 10's data to a new location (/var/lib/postgresql/10/main)
#Switch the ports of versions 10 and 12
#Move version 12's data to /data
#Put version 12 online
#Load up the data in version 12!
#Optionally wipe out the old installation
Shut it down:
pg_ctlcluster 12 master start
pg_lsclusters
#The cluster
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /data/postgres /var/log/postgresql/postgresql-10-main.log
12 master 5433 online postgres /var/lib/postgresql/12/master /var/log/postgresql/postgresql-12-master.logg
systemctl stop postgresql
systemctl status postgresql
Edit the config files:
vi /etc/postgresql/10/main/postgresql.conf
data_directory = '/var/lib/postgresql/10/main'
port = 5433
vi /etc/postgresql/12/master/postgresql.conf
data_directory = '/data/postgres'
port = 5432
listen_addresses = '*'
#While we are here do some performance tuning:
shared_buffers = 512MB
huge_pages = try
temp_buffers = 8G
work_mem = 4GB
maintenance_work_mem = 64
effective_cache_size = 384GB
#Note that I didn't reduce the number of connections (and the max_wal_senders, which must be < max connections), or change max_stack_depth (which gives an error if you set it too high)
vi /etc/postgresql/12/master/pg_hba.conf
Copy over the config to allow access from inside the network
Move the data:
df
#to check diskspace
rm -R /var/lib/postgresql/10/main
#Note that none of the config files in here were valid (though you should check this is true before you do it!)
rsync -av /data/postgres/ /var/lib/postgresql/10/main
#Takes awhile, but make sure it is all done before the next step
rm -R /data/postgres
rsync -av /var/lib/postgresql/12/master/ /data/postgres
systemctl start postgresql
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
10 main 5433 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
12 master 5432 online postgres /data/postgres /var/log/postgresql/postgresql-12-master.log
Do the installs for some extensions:
apt-get install postgresql-12-plr
apt-get install postgresql-plperl-12
postgresql-plpython3-12
Check it all works:
psql postgres
CREATE EXTENSION postgis;
CREATE EXTENSION plr;
CREATE EXTENSION plperl;
CREATE EXTENSION plpython3u;
\dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+---------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.0.0 | public | PostGIS geometry, geography, and raster spatial types and functions
(2 rows)
Make the user:
createuser --interactive researcher
Then restore the databases (as researcher in /bulk/backup):
createdb stockmarket
pg_restore -Fc -d stockmarket stockmarket_Fc_20201023.dump
createdb vcdb4
pg_restore -Fc -d vcdb4 vcdb4_Fc.dump
The restore threw some errors related to not having extension plpythonu, but otherwise seemed fine. The issue seems to be that pythonu is python2[https://www.postgresql.org/docs/12/plpython-python23.html], and python2 is not available for postgres 12 (it might be here: https://wiki.postgresql.org/wiki/Apt):
apt-cache search ".*plpython*.*"
====Other Fixes====
define( 'WPMS_SMTP_PASS', 'password generated by Google' );
define( 'WPMS_SMTP_AUTOTLS', true );
=====Author Comments=====
The blog supports multiple authors and by default, Wordpress emails an author whenever one of their posts gets a comment. If you'd like to disable author comment emails but keep the moderator emails, there's a simple fix:
'''Just go to wp-admin/options.php and set 'comments_notify' to 0.''' (See https://codex.wordpress.org/Option_Reference)
More complicated methods involve writing your own plugin [https://wordpress.stackexchange.com/questions/150125/disabling-comment-notifications-for-post-author] to refine wp_new_comment_notify_postauthor[https://developer.wordpress.org/reference/functions/wp_new_comment_notify_postauthor/] or changing the hooks[https://developer.wordpress.org/reference/hooks/notify_post_author/] used in wp-includes/comment.php:
$maybe_notify = apply_filters( 'notify_post_author', $maybe_notify, $comment_ID );
====Social Media Integration====
I tried to add a profile picture, but by default, WordPress uses [https://en.gravatar.com/ Gravitar], which, surprise, surprise, links to your WordPress.com account... and to add a self-hosted site, you have to install JetPack. At this point, I felt harassed and doubly so because I didn't install JetPack and yet, some how, the profile picture correctly updated from the one I'd posted on Gravitar. What the hecK?
===SEO===
I used Site Kit plugin for wordpess, and for mediawiki I made a sitemap to submit to Google.
See https://www.mediawiki.org/wiki/Manual:GenerateSitemap.php
In mediawiki:
mkdir sitemap
php maintenance/generateSitemap.php --memory-limit=50M --fspath=/var/www/html/mediawiki/sitemap/ --identifier=edegancom --urlpath=/sitemap/ --server=https://www.edegan.com --compress=yes
Then submit it to Google... I did this by making an alias in apache2.conf from sitemap to /var/www/html/mediawiki/sitemap/, then submitting
https://www.edegan.com/sitemap/sitemap-index-edegancom.xml
#in retrospect, I wish I'd used an identifier with 'wiki' in it but what the hey.
And with that success behind you, install Google XML Sitemaps on Wordpress, chose some settings (on Settings -> XML-Sitemap), and then post the URL to Google:
https://www.edegan.com/blog/sitemap.xml
It seems Yoast already builds a sitemap, you just need to submit to it Google... (I uninstalled XML Sitemaps):
https://www.edegan.com/blog/sitemap_index.xml
===HTTPS===
