vim search for ) replace with text then newline then )

Problem

I have a huge sql query with 574 lines and I needed to replace a closing bracket ) with text and then a new line with a closing bracket

Before:


) AS

After:


GROUP BY v.visit_id
) AS

Solution

Using vim’s search and replace feature


:%s/^)/GROUP BY v.visit_id\r)/gc

The above searches for the lines beginning with the closing bracket ) and then replaces it with text and then new line (\r) followed by a closing bracket )

References

How to replace a character by a newline in Vim

Add a newline after given patterns

delete gpg keys

Problem

My GPG Key Pair expired so needed to delete the existing one and replace with a new one

Solution

  • First list the gpg keys:

imela@whiscardz ~ $ gpg –list-keys
pub 4096R/5443F656 2018-09-06 [expired: 2019-09-06]
uid Whiscard imela (implementations encryption) <imela@whiscardz.org>

  • Delete the secret key first

imela@whiscardz ~ $ gpg –delete-secret-keys 5443F656
gpg (GnuPG) 1.4.20; Copyright (C) 2015 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

sec 4096R/5443F656 2018-09-06 Whiscard imela (implementations encryption) <imela@whiscardz.org>

Delete this key from the keyring? (y/N) y
This is a secret key! – really delete? (y/N) y

  • Then delete the public key

imela@whiscardz ~ $ gpg –delete-keys 5443F656
gpg (GnuPG) 1.4.20; Copyright (C) 2015 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

pub 4096R/5443F656 2018-09-06 Whiscard imela (implementations encryption) <imela@whiscardz.org>

Delete this key from the keyring? (y/N)y

References

https://serverfault.com/questions/214605/gpg-does-not-have-enough-entropy

https://www.howtoforge.com/helping-the-random-number-generator-to-gain-enough-entropy-with-rng-tools-debian-lenny

http://blog.chapagain.com.np/gpg-remove-keys-from-your-public-keyring/

 

Enable Windows Defender on Windows 7

Problem

Windows defender was not running and needed to activate it.

Solution

There is this nice youtube video explaining this process. Below is a summary:

  • Open Control Panel
  • Then Go to View by Categories and select Large icons:

Selection_999(1261).png

  • Then you should see windows defender at the bottom of the page:

Selection_999(1262).png

  • Once you click on it, it will ask you if you want to start it, click ok and you are good to go.

This program is blocked by group policy

Problem

I got this error when trying to enable windows defender from the control panel.

This program is blocked by group policy error message
This program is blocked by group policy

On doing some research it appears this may have been caused by a malware and a virus scan upon activating windows defender validated this suspicion.

Solution

The solution as shown in this youtube video was to delete policies from the registry. I did not use the batch file. Deleting the registry entries worked.

  • Reboot the machine and then press F8 as it is booting to go into safe mode.
  • Once its started from the start menu type Regedit to open the windows registry.
  • Then First create a backup of the registry by clicking File Followed by Export and save to a location of your choice.
  • Then Next delete the following registry entries. You can review this video in-case the screenshots below are not clear.

First regristry key to be deleted
First registry key to be deleted

Second regristry key to be deleted
Second registry key to be deleted

Third regristry key to be deleted
Third registry key to be deleted

Last registry key to be deleted
Last registry key to be deleted

References

How To Fix This Program is Blocked by Group Policy in Windows 7/8/10

Allow Remote Connections

Problem

Remote connections are needed for troubleshooting and also creating reports or you would probably just like to connection your postgres client to the database.

Solution

  • Check the ports that postgres is listening on:

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1336/postgres

  • Then edit the PostgreSQL configuration file and put the IP that you would like to allow. If you want to allow all IPs, then use 0.0.0.0

$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf

  • Then add the following line to end of the file and then save and quit.

host   all     all     0.0.0.0/0       md5

  • Next Allow TCP/IP socket. Open the config file below and change listen directive from localhost to * for allow all.

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

  • Search for the line, listen_addresses. Un-comment the line and then change localhost to * (you can use comma-separated list of addresses; * is all ip address)

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

listen_addresses = '*'          # what IP address(es) to listen on;

  • Restart postgres.

vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 1336 0.0 0.7 293436 24224 ? S Apr24 0:03 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 1359 0.0 1.1 293568 35892 ? Ss Apr24 0:01 postgres: checkpointer process
postgres 1360 0.0 0.1 293436 5596 ? Ss Apr24 0:01 postgres: writer process
postgres 1361 0.0 0.2 293436 8756 ? Ss Apr24 0:01 postgres: wal writer process
postgres 1362 0.0 0.2 293872 6532 ? Ss Apr24 0:01 postgres: autovacuum launcher process
postgres 1363 0.0 0.1 148948 4132 ? Ss Apr24 0:02 postgres: stats collector process
vagrant@ubuntu-xenial:~$ sudo service postgresql stop
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
vagrant 18972 0.0 0.0 12944 936 pts/0 S+ 11:30 0:00 grep --color=auto postgres
vagrant@ubuntu-xenial:~$ sudo service postgresql start
vagrant@ubuntu-xenial:~$ ps aux | grep postgres
postgres 19010 0.2 0.7 293436 24344 ? S 11:30 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 19012 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: checkpointer process
postgres 19013 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: writer process
postgres 19014 0.0 0.1 293436 3980 ? Ss 11:30 0:00 postgres: wal writer process
postgres 19015 0.0 0.2 293876 6740 ? Ss 11:30 0:00 postgres: autovacuum launcher process
postgres 19016 0.0 0.1 148948 4100 ? Ss 11:30 0:00 postgres: stats collector process
vagrant 19032 0.0 0.0 12944 1020 pts/0 S+ 11:30 0:00 grep --color=auto postgres

 

  • Then check if postgres is listening to all All connections

vagrant@ubuntu-xenial:~$ sudo netstat -tupln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1104/sshd
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 19010/postgres
tcp6 0 0 :::443 :::* LISTEN 1350/apache2
tcp6 0 0 :::80 :::* LISTEN 1350/apache2
tcp6 0 0 :::8083 :::* LISTEN 1350/apache2
tcp6 0 0 :::22 :::* LISTEN 1104/sshd
tcp6 0 0 :::5432 :::* LISTEN 19010/postgres
udp 0 0 0.0.0.0:68 0.0.0.0:* 856/dhclient

References

How Do I Enable remote access to PostgreSQL database server?

Installing virtualbox plus GUI ubuntu 16.04

Problem

Had virtual box installed and was using it from the command line. The process of creating a new virtual machine using this approach proved difficult. Tools like vagrant make this process easier but I needed to share this image with a non vagrant user.

Solution

Installed the default version of virtualbox that comes with ubuntu 16.04.

When I tried installing the latest version of virtual box, I would get the following error when starting a newly defined image:


Failed to open a session for the virtual machine bandago.

The virtual machine 'vmImage' has terminated unexpectedly during startup with exit code 1 (0x1).

Result Code: NS_ERROR_FAILURE (0x80004005)
Component: MachineWrap
Interface: IMachine {85cd948e-a71f-4289-281e-0ca7ad48cd89}

The solution maybe to after installing virtualbox, additionally installing the following packages:

(I never got to test this though)

vde2

virtualbox-guest-additions-iso

virtualbox-qt

Solution

Install virtualbox and the above packages:


imela@whiscardz ~ $ sudo apt-get install virtualbox virtualbox-guest-additions-iso virtualbox-qt
Reading package lists... Done
Building dependency tree
Reading state information... Done
virtualbox is already the newest version (5.1.38-dfsg-0ubuntu1.16.04.2).
The following NEW packages will be installed:
virtualbox-guest-additions-iso virtualbox-qt
0 upgraded, 2 newly installed, 0 to remove and 2 not upgraded.
Need to get 47.4 MB of archives.
After this operation, 87.4 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Note: After defining a new vm, when it’s time to link the ISO, double click on the vm definition and then select the iso.

References

Creating a Virtual Machine

Installing Oracle VM VirtualBox and Extension Packs

Oracle Linux_Downloads

How to Install VirtualBox 5.2 on Ubuntu 16.04 LTS

Exporting and Importing Virtual Box Images

Problem

Needed to save a virtual box image to be shared with someone else

Solution

Using vboxmanage command line to export the image and then test importing the image.

Steps

  • First list the virtual box images available

imela@whiscardz$ vboxmanage list vms
"test_default_1549514247520_18667" {6e29d56a-6531-4274-9d91-c6719e9fdaf3}
  • Then export the image you want to a file

imela@whiscardz$ vboxmanage export test_default_1549514247520_18667 -o test_default_1549514247520_18667.ova

  • Then copy it to the machine that you want to import the image.
  • Finally import the image.

imela@whiscardz2$ vboxmanage import test_default_1549514247520_18667.ova
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Potential Issues

When I tried exporting to a file name other than the name of the image, it failed and threw the error below (it could be the extention):


imela@whiscardz2$ vboxmanage export test_default_1549514247520_18667 --output otherFileName.ovf
0%...
Progress state: VBOX_E_IPRT_ERROR
VBoxManage: error: Appliance write failed
VBoxManage: error: RTVfsFsStrmAdd failed for 'otherFileName.ovf' (VERR_ALREADY_EXISTS)
VBoxManage: error: Details: code VBOX_E_IPRT_ERROR (0x80bb0005), component ApplianceWrap, interface IAppliance
VBoxManage: error: Context: "RTEXITCODE handleExportAppliance(HandlerArg*)" at line 1263 of file VBoxManageAppliance.cpp

References

How to export and import VirtualBox VM images?

VBoxManage registervm/unregistervm

Export to OVF

VBoxManage export

VBoxManage import

Speed insert or update query running on a large postgres table

Got this tip from the this post:

Problem

When you run a large query (insert/update) on a huge table with several indexes, these indexes can seriously slow the query execution.

Solution

With Postgresql it can be faster to disable the indexes before running the query and reindex all the table afterwards.

You can do it like this :

  1. Disable all table indexes
UPDATE pg_index
SET indisready=false
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Run your query
UPDATE <TABLE_NAME> SET ...;
  1. Re-enable all table indexes
UPDATE pg_index
SET indisready=true
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Re-index table