Friday, May 28, 2021
Thursday, March 11, 2021
Here are the basic steps to backup and restore Postgresql database. At this point of writing, I am using Postgresql 10 and 11 on Centos Linux 8.
In this article, backup database is inventory_system and restore of the backup is to another database that I will call cupboard.
Step 1: Login as postgres user
su - postgres
sudo su postgres -
Step 2: Dump the database content to a file using the tool pg_dump
pg_dump inventory_system > inventory_system-20210312.bak
or when database is located at IP 10.1.1.123 with port 5432
pg_dump -h 10.1.1.123 -p 5432 inventory_system > inventory_system-20210312.bak
Step 3: Create the new database
Step 4: Restore the database file with psql
psql cupboard < inventory_system-20210312.bak
On MS Windows 10 prompt that is provided through Laragon, the postgres user login is required
pg_dump -U postgres cm2020_1 > test.bak
Cron job is suitable to automate the backup process mentioned above. To schedule a cron job, use the command
Then save the following instruction to run at midnight of every Saturday
0 0 * * 6 pg_dump -U postgres inventory_system > ~/postgres/backups/inventory_system-20210312.bak
Save and exit from the text editor.
Refer to Cron activities in the file /var/log/cron
For automated backup, there is a good note with the scripts at Postgresql wiki.
Sunday, February 28, 2021
Having successfully installed a new Raspberry Pi with Raspbian OS, it is common that the next reboot the screen display just leaves a blank screen. You just know its the display setting run when at the boot screen the system messages can be seen.
Firstly, the most important file you must know is the /boot/config.txt
This contains many configuration, that require the file to be save and RPi to be restarted.
If your display is working, but the resolution is wrong, then access the start menu And look for Configuration, or directly access the resolution setting, type
When the display is a touch screen, it would be a good idea to install a virtual keyboard. Do this by
sudo apt install florence
Back to troubleshooting a display that's not showing. Always check your display is powered, and that system messages appear at boot before proceeding here.
Ensure RPi is connected to your local network, then SSH to your RPi. On MS Windows there is a software named Putty.
Determine your device supported resolutions. Use either of following commands;
tvservice -m CEA
tvservice -m DMT
Depending on whether you are connected to a TV (CEA) or computer monitor (DMT). Generally, a TV with VGA cable will use CEA.
Edit the file /boot/config.txt
Use the settings identified in Step 2. If you are using CEA then the value of hdmi_group=1,
if its DMT then hdmi_group=2.
Eg 1. when connected to a monitor with resolution 1280×720 and frequency 60Hz, these are the settings;
Eg 2. when connected to a TV through HDMI with resolution 640x480 and frequency 60Hz. Since the TV have a standard HDMI connector, hdmi_safe will be commented out. These are the settings;
Reference: Raspberry site
Thursday, February 18, 2021
All this while I have been changing table owners individually in a specific schema. One fine day, there was a requirement to transfer all tables to another owner without changing the database ownership.
The existing tables where having owner as "postgres", but to allow other application to access, a separate user is created and allowed access. Here is how its done to assign the user;
ALTER TABLE public.users OWNER TO contractors;
In the case where ALL tables are to have a new owner, then following can be done
select 'ALTER TABLE ' || table_name || ' OWNER TO contractors;' from information_schema.tables where table_schema = 'public' \gexec ;
Wednesday, February 10, 2021
Recently I encountered a problem to use JSONB type column with Reportserver and log it in their forum. Since I have made the example, why not post it here?
Here is an example to use JSONB type column in Postgresql 10 and newer.
Step 1: Create the sample databaseCREATE TABLE public.products (
id int4 NOT NULL PRIMARY KEY,
"name" varchar NOT NULL,
"product" jsonb NULL
Step 2: Add sample data
Step 3: Run an SQL querySELECT name, jsonb_agg(t->'name') AS brand, jsonb_agg(t->'value') AS code
FROM products, jsonb_array_elements(products.product) t GROUP BY name;
name |brand |code |
Apple Pie |["Apple Pie"] |["1All-1-1000-xzp"]|
Orange bod |["Orange Gala"] |["1Bll-1-99-aaa"] |
Chicken Pie|["Downtown Chicken Pie"]|["1Bll-1-201-aaa"] | SELECT name, jsonb_agg(t->'name') AS brand, jsonb_agg(t->'value') AS code
FROM products, jsonb_array_elements(products.product) t
WHERE t->>'name'='Orange Gala'
GROUP BY name
Friday, January 22, 2021
The scenario is, a table called assets was loaded through a script, but the column updated_by which should contain the user id, is stored with the user name instead. How to replace the updated_by field with the user id?
Here is an example of the table structure;
assets(id, name, description, updated_by)
Through the SQL command;
UPDATE assets t2
SET updated_by = t1.id
FROM users t1
WHERE t2.updated_by = t1.user_id;
Wednesday, January 13, 2021
ReportServer for Community is a Java based application that is available through Docker. There are options to install manually but some knowledge on Tomcat and Java is required.
The Docker (version 20.10.1) option installs on an image with Debian release 10 (Buster) with ReportServer version 3.3.0.
The ReportServer for community image is available at https://bitnami.com/stack/reportserver/containers
- Installed Docker version 20+
- You have create an account with docker and Docker is running with your signin.
On Windows, open powershell and go to your install folder. In my case its C:\users\tboxmy
Retrieve from the server required image
docker pull bitnami/reportserver
Yet to know if this line was useful, as I did not run docker-compose up -d
curl https://raw.githubusercontent.com/bitnami/bitnami-docker-reportserver-community/master/docker-compose.yml > docker-compose.yml
Configure network and database
docker network create reportserver-tier
docker run -d --name mariadb -e ALLOW_EMPTY_PASSWORD=yes -e MARIADB_USER=bn_reportserver -e MARIADB_DATABASE=bitnami_reportserver --net reportserver-tier --volume C:\Users\nasbo\mariadb-persistence:/bitnami bitnami/mariadb:latest
List the images
docker run -d --name reportserver-community -p 80:8080 -e ALLOW_EMPTY_PASSWORD=yes -e REPORTSERVER_DATABASE_USER=bn_reportserver -e REPORTSERVER_DATABASE_NAME=bitnami_reportserver --net reportserver-tier bitnami/reportserver:latest
Access ReportServer from a web browser
Use default username user and password as bitnami
- ► 2018 (20)
- ► 2017 (20)
- ► 2016 (24)
- ► 2015 (34)
- ► 2014 (24)
- ► 2013 (30)
- ► 2010 (29)
- ► 2009 (54)