Linking Microsoft Access and DBeaver To Postgres

If your desktop is Microsoft Windows, you may have Microsoft Access already installed. With an ODBC driver, you can link it to Postrgresql running on Linux.

To download the latest ODBC driver, go to https://www.postgresql.org/ftp/odbc/versions/ and choose "msi". Choose the most recent version for your Windows machine. If you don't know if your desktop is AMD or Intel, press the Windows Start button and type Settings. Click on Settings. In the windows that opens, click on Settings --> About. Look at the "Processor" line.

Download the zip file from https://www.postgresql.org/ftp/odbc/versions/msi to your PC.
Unzip or "Extract all" on the file. In this example, the file name is psqlodbc_12_02_0000-x86.

In the new folder, run the psqlodbc file. If Windows intercepts the install, press "More info" and click the button to "Run anyway".

The psqlODBC Setup Wizard should run.

Press the Microsoft Windows Start button and type: ODBC
Choose program "ODBC Data Sources".

Choose Add, then chose Postgresql and press button Finish. Set it up, press button Test, and save it.

In Microsoft Access, create a new blank database.

After creating the database, go to External Data and press New Data Source, From Other Sources, ODBC Database.

Link to the data source.

In the Machine Data Source tab, pick the connection that had just been set up.

Select the tables and press OK.

The table names will be on the left of Microsoft Access. Double-click to open the table data.



Another database management tool is  DBeaver. An open source version is available here.
Ubuntu users may install the snap (sudo snap install dbeaver-ce). Microsoft Windows users may install from the Windows Store.

Install PostgreSQL on Ubuntu 20.04

Let's install a recent version of Postgresql on Ubuntu 20.04. You will need a unix account with sudo privilege. At the end of this post we will do some introductory database commands.

Get familiar with the Linux install:
$ uname -a
Linux d990 5.4.0-37-generic #41-Ubuntu SMP Wed Jun 3 18:57:02 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
 
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04 LTS
Release:        20.04
Codename:       focal
 
$ df -k
Filesystem      1K-blocks     Used  Available Use% Mounted on
udev              8093132        0    8093132   0% /dev
tmpfs             1627360     1260    1626100   1% /run
/dev/sda2      1921800384 30477352 1793631080   2% /
tmpfs             8136796        0    8136796   0% /dev/shm
tmpfs                5120        0       5120   0% /run/lock
tmpfs             8136796        0    8136796   0% /sys/fs/cgroup
/dev/loop0         160000   160000          0 100% /snap/chromium/1165
/dev/loop2          56320    56320          0 100% /snap/core18/1754
/dev/loop3          63616    63616          0 100% /snap/gtk-common-themes/1506
/dev/loop1          56320    56320          0 100% /snap/core18/1705
/dev/loop7          27776    27776          0 100% /snap/snapd/7264
/dev/loop6          71040    71040          0 100% /snap/lxd/15457
/dev/loop4         160000   160000          0 100% /snap/chromium/1182
/dev/loop5          31104    31104          0 100% /snap/snapd/7777
/dev/loop8          71040    71040          0 100% /snap/lxd/15359
tmpfs             1627356        8    1627348   1% /run/user/1004


Update Ubuntu Linux:
$ sudo apt-get update
$ sudo apt-get upgrade


Read these instructions to set up apt to get the recent Postgresql release. Simply follow-along with the instructions from the link.

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get install postgresql

The command "install postgresql" will run for a minute or two. It should end with:

Success. You can now start the database server using:
    pg_ctlcluster 12 main start

Look at the new unix account "postgres". Note it does have a password to log in to unix:
$ cat /etc/group|tail -1
postgres:x:118:
$ cat /etc/passwd|tail -1
postgres:x:112:118:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

$ sudo grep postgres /etc/shadow
postgres:*:18427:0:99999:7:::


Look at what is running:
$ ps -fu postgres
UID          PID    PPID  C STIME TTY          TIME CMD
postgres  148055       1  0 21:06 ?        00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/po
postgres  148060  148055  0 21:06 ?        00:00:00 postgres: 12/main: checkpointer
postgres  148061  148055  0 21:06 ?        00:00:00 postgres: 12/main: background writer
postgres  148062  148055  0 21:06 ?        00:00:00 postgres: 12/main: walwriter
postgres  148063  148055  0 21:06 ?        00:00:00 postgres: 12/main: autovacuum launcher
postgres  148064  148055  0 21:06 ?        00:00:00 postgres: 12/main: stats collector
postgres  148065  148055  0 21:06 ?        00:00:00 postgres: 12/main: logical replication launcher
postgres  149432  149431  0 21:16 pts/1    00:00:00 -bash


Check the service manager to see if the database startup is automated:
$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2020-06-13 21:06:29 MDT; 14h ago
   Main PID: 147715 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18968)
     Memory: 0B
     CGroup: /system.slice/postgresql.service



To allow connections from outside the machine, edit the postgresql.conf file and add a line for listen_addresses. Then restart postgresql.
$ grep listen /etc/postgresql/12/main/postgresql.conf
listen_addresses = '*'
You may also need to edit pg_hba.conf, to allow connections from outside the machine.


Software versions:
$ psql -V
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg20.04+1)

Let's create a database, list the databases, create a table with a couple rows, and select from the table. From unix command-line, connect via psql:
$ psql
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
Type "help" for help.


postgres=# create database datadb;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 datadb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \c datadb
You are now connected to database "datadb" as user "postgres".
datadb=# create table testtable (columna text not null, columnb int not null);
CREATE TABLE
datadb=# insert into testtable values ('insertrowone', 1);
INSERT 0 1
datadb=# insert into testtable values ('insertrowtwo', 2);
INSERT 0 1
datadb=# select * from testtable;
   columna    | columnb
--------------+---------
 insertrowone |       1
 insertrowtwo |       2
(2 rows)

datadb=# \q

Ubuntu Linux High CPU For Swap Process

What do you do if you just installed a fresh Ubuntu 20.04 server, and after installing some packages with "apt" you notice high CPU usage from the swap process?

If "top" shows kswapd0 persistently using high CPU, and "freemem -d" and swap are ok, you can try to adjust the swappiness in file sysctl.conf and reboot.
$ cat /proc/sys/vm/swappiness
60
$ sudo vi /etc/sysctl.conf
$ cat /etc/sysctl.conf | grep vm
vm.swappiness=10

Changing swappiness didn't fix this problem of high CPU usage. Let's dig deep.

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04 LTS
Release:        20.04
Codename:       focal
install@d990 ~ $ uname -a
Linux d990 5.4.0-29-generic #33-Ubuntu SMP Wed Apr 29 14:32:27 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Look closely at "top" output.
$ top
top - 19:03:26 up 7 min,  3 users,  load average: 3.09, 2.72, 1.44
Tasks: 132 total,   1 running, 131 sleeping,   0 stopped,   0 zombie
%Cpu(s): 76.3 us,  0.4 sy,  0.0 ni, 23.2 id,  0.1 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :  15892.2 total,  10206.3 free,   4412.8 used,   1273.1 buff/cache
MiB Swap:   4096.0 total,   4096.0 free,      0.0 used.  11199.7 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   1071 kevin     20   0 2435108   2.3g   1480 S 300.0  14.7  20:50.80 kswapd0   1147 minec     20   0 7861400   1.9g  28544 S   6.6  11.9   1:54.40 java
      1 root      20   0  167604  11524   8368 S   0.0   0.1   0:00.98 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.00 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par+
      6 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 kworker+
      8 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 mm_perc+
      9 root      20   0       0      0      0 S   0.0   0.0   0:00.01 ksoftir+
     10 root      20   0       0      0      0 I   0.0   0.0   0:00.13 rcu_sch+
     11 root      rt   0       0      0      0 S   0.0   0.0   0:00.00 migrati+
     12 root     -51   0       0      0      0 S   0.0   0.0   0:00.00 idle_in+
     13 root      20   0       0      0      0 I   0.0   0.0   0:00.01 kworker+
     14 root      20   0       0      0      0 S   0.0   0.0   0:00.00 cpuhp/0
     15 root      20   0       0      0      0 S   0.0   0.0   0:00.00 cpuhp/1
     16 root     -51   0       0      0      0 S   0.0   0.0   0:00.00 idle_in+
     17 root      rt   0       0      0      0 S   0.0   0.0   0:00.14 migrati+

$ top -u kevin
top - 19:03:59 up 8 min,  3 users,  load average: 3.05, 2.75, 1.49
Tasks: 132 total,   1 running, 131 sleeping,   0 stopped,   0 zombie
%Cpu(s): 76.3 us,  0.3 sy,  0.0 ni, 23.1 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :  15892.2 total,  10205.5 free,   4413.5 used,   1273.2 buff/cache
MiB Swap:   4096.0 total,   4096.0 free,      0.0 used.  11199.0 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   1071 kevin     20   0 2435108   2.3g   1480 S 300.3  14.7  22:28.66 kswapd0
   1015 kevin     20   0   14368   6760   2800 S   0.0   0.0   0:00.00 rsync


Why is kevin in charge of swap? Kevin has yet to log in to the system.
$ last kevin

wtmp begins Sat May  9 18:16:21 2020
 
$ groups kevin
kevin : kevin
 
$ sudo grep kevin /etc/sudoers
 
$ ps -fu kevin
UID          PID    PPID  C STIME TTY          TIME CMD
kevin       1015       1  0 18:56 ?        00:00:00 rsync
kevin       1071       1 99 18:56 ?        00:11:53 ./kswapd0


We know kevin has not logged in, is only in his own group, and does not have sudo. This was the most recent account we created on the machine.
$ tail -1 /etc/passwd
kevin:x:1005:1004:,,,,novice tech learner:/home/kevin:/bin/bash


Comment out the entry in the passwd file.
$ tail -1 /etc/passwd
kevin:x:1005:1004:,,,,novice tech learner:/home/kevin:/bin/bash
 
$ sudo vi /etc/passwd
 
$ tail -1 /etc/passwd
#kevin:x:1005:1004:,,,,novice tech learner:/home/kevin:/bin/bash

Run top, and it won't know the "kevin" username for uid 1005. It is still consuming CPU.
$ top
top - 19:08:43 up 13 min,  3 users,  load average: 3.13, 2.96, 1.92
Tasks: 130 total,   1 running, 129 sleeping,   0 stopped,   0 zombie
%Cpu(s): 76.2 us,  0.6 sy,  0.0 ni, 23.0 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :  15892.2 total,  10198.5 free,   4413.8 used,   1279.9 buff/cache
MiB Swap:   4096.0 total,   4096.0 free,      0.0 used.  11198.6 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   1071 1005      20   0 2435108   2.3g   1480 S 300.7  14.7  36:40.32 kswapd0
   1147 minec     20   0 7861400   1.9g  28544 S   6.7  11.9   2:14.81 java
    375 root      20   0       0      0      0 S   0.3   0.0   0:00.01 jbd2/sd+
      1 root      20   0  167604  11524   8368 S   0.0   0.1   0:01.00 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.00 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par+
      6 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 kworker+
      8 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 mm_perc+
 

With the passwd entry for kevin commented out, let's reboot and observe what happens.
$ sudo systemctl reboot

$ top
top - 19:14:04 up 1 min,  1 user,  load average: 1.35, 0.61, 0.23
Tasks: 138 total,   1 running, 137 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.3 us,  0.4 sy,  0.0 ni, 99.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :  15892.2 total,  12795.1 free,   1850.3 used,   1246.8 buff/cache
MiB Swap:   4096.0 total,   4096.0 free,      0.0 used.  13763.3 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
    898 minec     20   0 7861400   1.7g  28288 S   6.3  10.7   1:20.07 java
    156 root      20   0       0      0      0 I   0.3   0.0   0:00.16 kworker+
    443 root      19  -1  133560  61216  60108 S   0.3   0.4   0:00.61 systemd+
   1206 root      20   0   13416   8268   7096 S   0.3   0.1   0:00.01 sshd
   1207 sshd      20   0   12160   4616   3708 S   0.3   0.0   0:00.01 sshd
      1 root      20   0  167744  11508   8440 S   0.0   0.1   0:03.25 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.00 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par+

Let's remove the kevin account properly. Uncomment the line in /etc/passwd and delete the account.
$ sudo vi /etc/passwd
 
$ sudo userdel -r kevin
$ grep kevin /etc/passwd
$ uptime
 19:15:53 up 3 min,  1 user,  load average: 0.21, 0.42, 0.20

Reboot and look for normal functioning.
$ sudo systemctl reboot