System Administration

System Administration information

•

PostgreSQL has an autovacuum and however much it improves with every version it has not removed the benefit of the occasional manual vacuum. There are 3 levels of effectiveness and load; autovacuum, vacuum (like reindex), and vacuum full (like a dump/restore). We find that a full vacuum can be of little benefit even over decades, but a regular vacuum can be of benefit in as little as a year.

  • System Tools > Analytics > DB Index Fragmentation

gives an indication of if running a vacuum would be of benefit (more than 10 tables, over 30% fragmented). Noodle has a button for this and other tasks in

  • System Tools > Settings > General > Import > Maintenance

But there is no progress report etc like with the official management client for PostgreSQL (PGAdmin4): 

or by running SQL:

  • VACUUM VERBOSE ANALYZE;

We don't automate this for servers we don't manage because it can conflict with backups and usage.

•

If certbot or the like is not used certificates will need to be updated manually.

  • Download the signed public key from your Certificate Authorities (CA)
  • Import the signed public key into the keystore already containing the private key (and intermediate certificates if required)
keytool -import -alias noodle.domain.tld -trustcacerts -file noodlecert.crt -keystore noodle.pfx
  • Restart the Noodle service

•

Add the following to the Host tag in server.xml

<Context docBase="ROOT" override="true">
    <Resources cachingAllowed="true" cacheMaxSize="100000"/>
</Context>

Add the following to multiserver or intranet.bat

-Xmx1000m -Xms1000m -XX:+UseG1GC

•

Add the following to the Host tag in server.xml

<Context docBase="ROOT" override="true">
    <Resources cachingAllowed="false" />
</Context>

Add the following to multiserver or intranet.bat

-Xmx50m -Xms50m -XX:+UseG1GC

•

cert.sh

certbot renew --quiet --manual --preferred-challenges dns \
--manual-public-ip-logging-ok \
--manual-auth-hook cert_dns.sh \
--deploy-hook cert_deploy.sh

cert_dns.sh

ssh $DNS "nsupdate <( echo \"server 127.0.0.1
update delete _acme-challenge.$CERTBOT_DOMAIN.
update add _acme-challenge.$CERTBOT_DOMAIN. 3600 TXT $CERTBOT_VALIDATION
send
quit\" )"
sleep 10

See letsencrypt for other options.

• 
dnf install -y epel-release
dnf install -y tar postgresql-server postgresql-contrib java-11-openjdk

#optional
dnf install -y vim tmux bc id3lib html2text p7zip-plugins libjpeg-turbo poppler-utils unzip netpbm-progs perl-Image-ExifTool
#optional manual install catdoc unrtf ffmpeg

newcfg (){
	F="$1"
	chown --reference="$F" "$F".new
	chmod --reference="$F" "$F".new
	mv -f "$F".new "$F"
}

#PostgreSQL
/usr/bin/postgresql-setup --initdb --unit postgresql
systemctl enable postgresql
TMP=/var/lib/pgsql/data/pg_hba.conf
cp $TMP $TMP.original
cat $TMP | grep -vP "^ *host.*127" > $TMP.new
echo -e "host\tall\tall\t127.0.0.1/32\tpassword" >> $TMP.new
newcfg $TMP
TMP=/var/lib/pgsql/data/postgresql.conf
cp $TMP $TMP.original
MT=$(cat /proc/meminfo | grep MemTotal | perl -pe 's/^[^ ]* *([0-9]+) *kB$/$1\/1000/g');
#java+linux=(512+256)
ECS=$(echo "($MT-(512+256))/2" | bc);
SB=$(echo "($MT-(512+256))/4" | bc);
cat $TMP | perl -pe 's/^#?(effective_cache_size)[ \t]*=[ \t]*[^ \t]+([ \t].*)?$/$1 = '$ECS'MB$2/g' \
| perl -pe 's/^#?(shared_buffers)[ \t]*=[ \t]*[^ \t]+([ \t].*)?$/$1 = '$SB'MB$2/g' \
| perl -pe 's/^#?(max_locks_per_transaction)[ \t]*=[ \t]*[^ \t]+([ \t].*)?$/$1 = 512$2/g' \
| perl -pe 's/^#?(max_connections)[ \t]*=[ \t]*[^ \t]+([ \t].*)?$/$1 = 60$2/g' \
| perl -pe 's/^#?(checkpoint_segments)[ \t]*=[ \t]*[^ \t]+([ \t].*)?$/$1 = 30$2/g' \
| perl -pe 's/^#?(log_min_duration_statement)[ \t]*=[ \t]*[^ \t]+([ \t].*)?$/$1 = 30000$2/g' \
| perl -pe 's/^#?(log_line_prefix)[ \t]*=[ \t]*[^#]+(#.*)?$/$1 = '\''%m: '\''\t\t$2/g' \
> $TMP.new
newcfg $TMP
systemctl start postgresql

#Noodle
cd /opt
tar -zxf ./Noodle.tar.gz
su postgres -c 'cd;/usr/bin/psql -d postgres -U postgres --file /opt/Noodle/init.sql' 
cd Noodle
cp noodle.service /lib/systemd/system/
systemctl enable noodle
systemctl start noodle

Other Linux Installation examples available.

•

Any user with access to system tools can reset the admin password with a web browser.

Any person with access to the SQL server can change the admin password to another users password

select object_id, username, password from users where username = 'admin' or username = '$ME';
update users set password = '$PASS' where object_id = '$ID';

For example

update users set password = 'P3b*cH.5fOBTJl5ELM)W' where username = 'admin';

•

The Business Continuity Plan for local installs is mostly up to the client and we provide a site export feature by which all data is converted to standard formats in a zip file to avoid vendor lock-in. We also have a HA option. and restoring from backups can be tested in a virtual machine.

Hosted instances have security measures in place and can be restored to any cloud or local environment. We maintain backup domains with multiple registries.