Grab a copy of Spydish and tweak your Microsoft Windows 10 privacy settings. Take control of your PC.
Thursday, July 16, 2020
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.
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.
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.
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
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
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
Subscribe to:
Posts (Atom)