Wednesday, June 17, 2020

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.





















Saturday, June 13, 2020

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