How to Install Multiple java on old linux ditribution

Sometimes we need to install several versions of Java on Linux which are quite old
Managing multiple Java versions on old Linux can be achieved using various methods, including manual installation and configuration with update-alternatives

Installation and Configuration:

Download and Extract:
Download the desired JDK/JRE binary from Oracle's website. Extract the archive to a specific directory (e.g., /opt/java/jdk17.0_version). Repeat for each Java version you want to install.

Set JAVA_HOME:
For a specific application or user, you can set the JAVA_HOME environment variable to point to the desired Java installation directory. This is typically done in the user's .bashrc or .profile file.

# export JAVA_HOME=/opt/java/jdk17.0_version # export PATH=$JAVA_HOME/bin:$PATH ## Repeat for other Java executables like 'jar', 'javadoc', etc.

Update System-Wide Alternatives:
To make a specific Java version the default for all users, use the update-alternatives command.

# update-alternatives --install "/usr/bin/java" "java" "/opt/java/jdk17.0_version/bin/java" 1 # update-alternatives --install "/usr/bin/jar" "jar" "/opt/java/jdk17.0_version/bin/jar" 1 # update-alternatives --install "/usr/bin/javac" "javac" "/opt/java/jdk17.0_version/bin/javac" 1 # # Repeat for other Java executables like 'jar', 'javadoc', etc.

The last argument 1 is the priority, which determines the default if multiple alternatives exist. Higher priority takes precedence.

Switching Versions:
To switch the default Java version system-wide, use:

# update-alternatives --config java

This will present a list of available Java installations, allowing you to choose the desired default by entering its corresponding number.

Important Notes:

• Be mindful of the licensing terms for Oracle JDK, as older versions might have different licensing models. OpenJDK is a free and open-source alternative.

• When using update-alternatives, ensure you add all relevant Java executables (e.g., java, javac, jar) to avoid inconsistencies.

• Test your Java applications after changing the default Java version to ensure compatibility.

Best practices Oracle big data backup and recovery

Backup and recovery for Oracle systems with large data volumes (big data) requires a different approach than small systems. Here are best practices for Oracle backup and recovery at big data scale:

1. Use RMAN (Recovery Manager)

Why RMAN?

* Native tool from Oracle * Efficient in incremental backup and compression * Can do parallelism * Support granular and fast recovery

Best Practices:

* Use Incremental Level 1 backups daily * Create Level 0 full weekly backup * Enable Block Change Tracking to speed up incremental backups. * Combine with compression (RMAN COMPRESSED BACKUPSET) * Use RMAN Catalog for long-term backup metadata management*

2. Partitioning & Subsetting

For large tables:

* Use Partition Table per time (month/year) → easy to backup and recover partially * Backups can be done per partition using DATA PUMP or RMAN per tablespace.

3. Implement Data Guard / Standby

Oracle Data Guard:

* Create a standby database (physical/logical) * Can be used as read-only for reporting * Can directly promote as primary if primary is down

For big data, this provides fast failover and minimal downtime.


4. Backup ke Cloud atau Disk Terpisah

* Use Oracle Secure Backup (OSB) for backup to tape/cloud * Or use direct backup to cloud storage (Oracle Cloud, S3-compatible) * Make sure the backup location is different from primary storage.

5. Data Pump (expdp/impdp) untuk Logical Backup

Gunakan untuk:

* Schema backup * Backup data subsets (partitions, tables) * Migration between systems

Tips:

* Use the PARALLEL parameter, DIRECT_PATH=Y * Split output files across multiple disks (multiplexing)

6. Documented & Tested Recovery Plan

Must:

Documentation for recovery from:

* RMAN backup * Data Guard failover * Flashback Database (if active)

Simulate recovery periodically:

* Create scenarios: lost tablespace, data corruption, human error, crash

7. Flashback Technology (optional but powerful)

If using Enterprise Edition:

* Flashback Table * Flashback Database * Flashback Drop (Recyclebin)

Very useful for recovery from human error without restoring the full database.


8. Monitoring & Automation

* Use Oracle Enterprise Manager (OEM) for backup & restore monitoring * Schedule backup with cron/Scheduler * Ensure alerts if backup fails (email, webhook, etc.)

Daily Big Data Backup Strategy Example:

Day	Backup Type	Tool
Monday - Saturday	Incremental (Level 1)	RMAN
Sunday	Full Backup (Level 0)	RMAN
Every day	Logical subset (optional)	Data Pump
Weekly	Export Metadata Only	Data Pump
Monthly	Archiving ke Cloud/Tape	RMAN

Here is an example backup scenario for Oracle big data using RMAN and Data Pump with best practices.

1. RMAN Backup: Daily and Weekly Strategy

Initial Setup

Enable Block Change Tracking to make incremental backups faster:

sql ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/bct.f';

Weekly: Full Level 0 Backup (Sunday)

bash rman target / RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;   BACKUP INCREMENTAL LEVEL 0 DATABASE     TAG 'FULL_WEEKLY'     FORMAT '/backup/rman/full_%d_%T_%U.bkp';   BACKUP CURRENT CONTROLFILE     FORMAT '/backup/rman/ctl_%d_%T_%U.bkp';   BACKUP SPFILE     FORMAT '/backup/rman/spf_%d_%T_%U.bkp';   RELEASE CHANNEL ch1; }

Daily: Incremental Level 1 Backup (Monday–Saturday)

bash rman target / RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;   BACKUP INCREMENTAL LEVEL 1 DATABASE     TAG 'INCR_LEVEL_1'     FORMAT '/backup/rman/incr_%d_%T_%U.bkp';   BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES     FORMAT '/backup/rman/arch_%d_%T_%U.bkp'     DELETE INPUT;   RELEASE CHANNEL ch1; }

Restore Simulation (Summary)

bash rman target / STARTUP MOUNT; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN;

2. Data Pump Backup: Logical Export

Use this for logical backups (schema, tables, etc.)


Backup 1 Scheme (eg SCOTT)

bash expdp system/password directory=DATA_PUMP_DIR \   dumpfile=SCOTT_%DATE:~0,10%.dmp logfile=SCOTT_exp.log \   schemas=SCOTT parallel=4 compression=all

Backup 1 Large Table (partitioned)

bash expdp system/password directory=DATA_PUMP_DIR \   dumpfile=SALES_%DATE:~0,10%.dmp logfile=SALES_exp.log \   tables=SALES query="WHERE sales_date >= TO_DATE('01-APR-2025','DD-MON-YYYY')" \   parallel=4 compression=all

Restore (Import) Example

bash impdp system/password directory=DATA_PUMP_DIR \   dumpfile=SCOTT_2025-04-23.dmp \   remap_schema=scott:scott_test \   logfile=SCOTT_imp.log

Combination Tips:

* Use RMAN for fast disaster recovery and restore * Use Data Pump for logical backups and migrations (between servers/sandboxes) * Logical backups can run in parallel and be compressed * RMAN backups are stored on disk or cloud (via SBT library or Oracle Cloud Backup)

Here is a complete example of a shell script + cron job to perform an Oracle backup using RMAN and Data Pump, with daily and weekly scenarios respectively.

Backup Directory Structure

Example backup directory:

bash /backup/oracle/rman/ /backup/oracle/datapump/ /log/oracle/backup/

# for RMAN backups # for export Data Pump # for backup logs

1. RMAN Backup Scripts: backup_rman.sh

bash #!/bin/bash DATE=$(date +%F) BKUP_DIR="/backup/oracle/rman" LOG_DIR="/log/oracle/backup" ORACLE_SID=ORCL export ORACLE_SID mkdir -p $BKUP_DIR $LOG_DIR rman target / log="$LOG_DIR/rman_backup_$DATE.log" <<EOF RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;   BACKUP INCREMENTAL LEVEL 1 DATABASE     TAG 'INCR_LEVEL_1'     FORMAT '$BKUP_DIR/incr_%d_$DATE_%U.bkp';   BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES     FORMAT '$BKUP_DIR/arch_%d_$DATE_%U.bkp'     DELETE INPUT;   BACKUP CURRENT CONTROLFILE     FORMAT '$BKUP_DIR/ctl_%d_$DATE_%U.bkp';   RELEASE CHANNEL ch1; } EOF

2. Data Pump Scripts: backup_datapump.sh

bash #!/bin/bash DATE=$(date +%F) BKUP_DIR="/backup/oracle/datapump" LOG_DIR="/log/oracle/backup" ORACLE_SID=ORCL export ORACLE_SID mkdir -p $BKUP_DIR $LOG_DIR expdp system/password directory=DATA_PUMP_DIR \   dumpfile=SCOTT_${DATE}.dmp \   logfile=$LOG_DIR/datapump_SCOTT_${DATE}.log \   schemas=SCOTT parallel=4 compression=all

3. Add to Crontab

Edit crontab:

bash crontab -e

RMAN Daily Schedule (01:00 AM)

cron 0 1 * * 1-6 /path/to/backup_rman.sh

Data Pump Weekly Schedule (every Sunday at 02:00)

cron 0 2 * * 0 /path/to/backup_datapump.sh

Important Tips:

* Test the script first with bash backup_rman.sh before cronizing it. * Make sure the user running the cron job (oracle, backup, etc.) has access to ORACLE_HOME, ORACLE_SID, and the rman, expdp path. * Jangan lupa bersihkan backup lama secara berkala (pakai find ... -mtime +X -delete)

Delete RMAN backups

1. Backup is no longer needed

You may delete RMAN backups if:

* Already have a more recent backup (e.g. weekly level 0 backup). * Not being used by recovery window. * Not required for point-in-time recovery.

2. Do Not Manually Delete Files from Disk

Do not directly delete .bkp files from a folder like using rm. That can cause the RMAN catalog to become unsynchronized.

Use the official RMAN command:

rman DELETE BACKUP OF DATABASE; DELETE BACKUP OF ARCHIVELOG ALL; DELETE EXPIRED BACKUP;

Example: Delete Backups Older Than 14 Days

bash rman target / DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-14'; DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7';

Check the Saved Backup First

sql LIST BACKUP SUMMARY; LIST BACKUP OF DATABASE;

Add in Cron Job (Automatic Cleanup)

bash rman target / <<EOF DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-14'; EOF

Additional Tips:

Use RECOVERY WINDOW for automatic setup:

rman CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

RMAN will automatically know which backups are "expired" if there is a retention policy.


Shell script to automatically delete old RMAN backups, suitable to run via weekly cron job for example.

Script: cleanup_rman_backup.sh

bash #!/bin/bash # Setup environment ORACLE_SID=ORCL export ORACLE_SID LOG_DIR="/log/oracle/backup" mkdir -p $LOG_DIR DATE=$(date +%F) rman target / log="$LOG_DIR/rman_cleanup_$DATE.log" <<EOF -- Konfigurasi retensi (kalau belum diatur) CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; -- Hapus backup yang tidak dibutuhkan lagi (berdasarkan retensi) DELETE NOPROMPT OBSOLETE; -- Optional: hapus archive log lama (jika tidak pakai FRA) DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DISK; -- Bersihkan backup expired dari katalog CROSSCHECK BACKUP; DELETE NOPROMPT EXPIRED BACKUP; EOF

Notes:

* This script does not delete files directly. It only deletes officially obsolete or expired backups from the RMAN catalog. * Safe to use and supports automatic retention.

Weekly Cron Job Example (Every Sunday Morning at 3am)

bash 0 3 * * 0 /path/to/cleanup_rman_backup.sh

If you use Fast Recovery Area (FRA), RMAN can also automatically clean up old files, but this script is still important for cleaning up irrelevant catalogs and backups.

How to Change time and date language to english CentOS 7

How to Change time and date language to english CentOS 7

Check current setting

$ locale

	Output
	LANG=id_ID.UTF-8 LC_CTYPE="id_ID.UTF-8" LC_NUMERIC="id_ID.UTF-8" LC_TIME="id_ID.UTF-8" LC_COLLATE="id_ID.UTF-8" LC_MONETARY=id_ID.UTF-8 LC_MESSAGES="id_ID.UTF-8" LC_PAPER="id_ID.UTF-8" LC_NAME="id_ID.UTF-8" LC_ADDRESS="id_ID.UTF-8" LC_TELEPHONE="id_ID.UTF-8" LC_MEASUREMENT="id_ID.UTF-8" LC_IDENTIFICATION="id_ID.UTF-8" LC_ALL=

Check what locale are available on your system you can use “localectl” command with “list-locales” option

$ localectl list-locales |grep en_

To verify the current locale configuration. So you can change this file and choose a language from previews step:

# vi /etc/locale.conf

	Output
	LANG=id_ID.UTF-8

The language can also be changed by using “localectl”:

# localectl set-locale LANG=en_US.UTF-8

Also add this to /etc/environment

# vi /etc/environment

	Output
	LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY=en_US.UTF-8 LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL=

After that, just logout and login for the new configuration take effect

How To Install NRPE on CentOS 7

How To Install NRPE on CentOS 7


Prerequisites

Before installing NRPE, you must have the following prerequisites:

• A server running CentOS 7.

• A user account with sudo or root privileges.

Step 1: Install NRPE Package

To install the NRPE package, run the following command as root or with sudo privileges:

$ sudo yum install nrpe nagios-plugins-nrpe -y

Step 2: Configure NRPE

After the installation is complete, you need to configure NRPE. The NRPE configuration file is located at /etc/nagios/nrpe.cfg. Open the file using your favorite text editor and make the following changes:

• Change the allowed_hosts directive to allow connections from your Nagios server. For example, if your Nagios server has an IP address of 192.168.1.100, you would add the following line to the configuration file:

allowed_hosts=192.168.1.100

• Define the commands that NRPE will execute. The commands are defined in the command[ ] sections of the configuration file. For example, to define a command to check the load average on the remote host, you would add the following line to the configuration file:

command[check_root_disk]=/usr/lib64/nagios/plugins/check_disk -w 20% -c 10% -p /

Step 3: Start and Enable NRPE Service

To start and enable the NRPE service, run the following commands:

$ sudo systemctl start nrpe $ sudo systemctl enable nrpe

Step 4: Test NRPE Configuration

Finally, you can test the NRPE configuration by running the following command on your Nagios server:

$ /usr/lib64/nagios/plugins/check_nrpe -H

If the NRPE configuration is correct, the output should be similar to the following:

	Output
	NRPE v4.1.0

Conclusion

In this article, we showed you how to install NRPE on CentOS 7. NRPE allows you to monitor resources on remote hosts from your Nagios server. With the steps outlined in this article, you should be able to install NRPE on your server and get started with monitoring your network.

How to Create Lvm in Linux Step by Step

How to Create Lvm in Linux Step by Step

Step 1: Add Harddrive to System

Check with dmesg

[admin@ora87 ~]$ sudo dmesg

Step 2: Create a new Partiton using cfdisk tool :

[admin@ora87 ~]$ sudo cfdisk /dev/sda

Step 3: Initializes the partition /dev/sda1 as an LVM physical volume :

[root@ora87 ~]# pvcreate /dev/sda1

Step 4: Scanning for Block Devices

[root@ora87 ~]# lvmdiskscan

Step 5: Displaying Physical Volumes :

There are three commands you can use to display properties of LVM physical volumes: pvs, pvdisplay, and pvscan.

The pvdisplay command provides a verbose multi-line output for each physical volume. It displays physical properties
(size, extents, volume group, etc.) in a fixed format.

[root@ora87 ~]# pvdisplay

The pvscan command scans all supported LVM block devices in the system for physical volumes

[root@ora87 ~]# pvscan

Step 6: Create volume group name oradata and add /dev/sda1 partition into the group.

[root@ora87 ~]# vgcreate vg_oradata /dev/sda1

If you have more than one partition, you can add multiple partition in single command. This command creates a local volume
named vg_newlvm that contains physical volumes /dev/sda1 and /dev/sdb1 :

[root@ora87 ~]# vgcreate vg_oradata /dev/sda1  /dev/sdb1

Step 7: Creates a logical volume called oradata_vol that uses all of the unallocated space in the volume group vg_oradata :

[root@ora87 ~]# lvcreate --name oradata_vol -l 100%FREE vg_oradata

Step 8: Display the created logical volumes :

[root@ora87 ~]# lvdisplay

Step 9: Use the mkfs command to format a newly created LVM :

[root@ora87 ~]# mkfs.ext4 /dev/vg_oradata/oradata_vol

Step 10: Create the mount point and mount the new LVM :

[root@ora87 ~]# mkdir -p /oradata

[root@ora87 ~]# mount /dev/vg_oradata/oradata_vol /oradata

Step 11: Verify thew new disk layout :

[root@ora87 ~]# df -h

Firefox browser - bypassing the print dialog box

Please notice that on firefox browsers we have an option to bypass the pop-up dialog box when printing a boarding pass (or any other printout from the system).

This option might come handy when there is a need for an automated-fast print during the check-in process.

Please notice that this change explained below is on the browser and not on the CRS system, and this change will affect all sites visited using the Firefox browser and is not limited only to working on the system.

IMPORTANT: This option is best if you only have a single printer connected to the computer you are working on. If you are regularly switching between printers at a station, having that dialog box pop up every time is likely a better option, so we would highly recommend NOT doing this with multiple printers connected.

Instructions:

• Open a new tab, paste about:config into the address bar and hit enter.
• If you see the “This Might Void Your Warranty” page, click the blue “I accept the risk!” button. Understand we are manually modifying Firefox’s default settings.
• In the Search box at the top, paste "print.always_print_silent"
  
• Double click the setting to change it to "true"
  
• Done!
  

All printouts from the Firefox browser on this computer will be automatically printed to the default printer set on that computer without the printer selection pop-up screen will appear.

How To Disable Firefox Insecure Form Warning

Keep in mind that your password can very easily be stolen if you are logging into websites over HTTP. If a website you use does not offer secure logins, use a unique password for that site and do not use it anywhere else.

Only follow these instructions if you want to turn off these important security settings in Firefox. Do not follow these instructions if someone else is telling you to.

Here’s how to disable Firefox insecure password warnings:

• Open a new tab, paste about:config into the address bar and hit enter.
• If you see the “This Might Void Your Warranty” page, click the blue “I accept the risk!” button. Understand we are manually modifying Firefox’s default settings.
• In the Search box at the top, paste "security.insecure_field_warning.contextual.enabled"
• Double click the setting to change it to "false", to disable Firefox’s insecure password warning.
• Done! Now when you visit pages with HTTP login forms, the warning will no longer appear.

 
If you also want to restore autofill functionality, so that your saved login/password automatically populates in an HTTP form, keep the configuration page open and follow the next step.

Optional. In the Search Box on the about:config page, paste signon.autofillForms.http

Double click the setting to change it to “true,” this will enable autofill.

Install Postgresql from source on CentOS 7

In this article, let us review how to install postgreSQL 9.2.24 database on Centos 7 from source code.


Step 1: Download postgreSQL source code

Download From the postgreSQL download site.

# wget https://ftp.postgresql.org/pub/source/v9.2.24/postgresql-9.2.24.tar.gz

Step 2: Install postgreSQL

# tar xvfz postgresql-9.2.24.tar.gz # cd postgresql-9.2.24 # ./configure # make # make install

PostgreSQL Installation Issue:

You may encounter the following error message while performing ./configure during postgreSQL installation.

# ./configure checking for -lreadline... no checking for -ledit... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure.  It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support.

PostgreSQL Installation Solution:

Install the readline-devel and libtermcap-devel to solve the above issue.

# yum install libtermcap-devel readline-devel

Step 3: Verify the postgreSQL directory structure

After the installation, make sure bin, doc, include, lib, man and share directories are created under the default /usr/local/pgsql directory as shown below.

# ls -l /usr/local/pgsql/ total 14 drwxr-xr-x 2 root root 4096 Jan 22 10:24 bin lrwxrwxrwx 1 root root 4096 Jan 22 14:17 data drwxr-xr-x 6 root root 4096 Jan 22 10:24 include drwxr-xr-x 3 root root 4096 Jan 22 10:24 lib drwxr-xr-x 6 root root 4096 Jan 22 13:28 share

Step 4: Create postgreSQL user account

# adduser postgres # passwd postgres Changing password for user postgres. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully.

Step 5: Create postgreSQL data directory

Create the postgres data directory and make postgres user as the owner.

# mkdir /usr/local/pgsql/data # chown postgres:postgres /usr/local/pgsql/data # ls -ld /usr/local/pgsql/data drwxr-xr-x 2 postgres postgres 19 Jan 22 14:17 /home/postgres/data

Step 6: Initialize postgreSQL data directory

Before you can start creating any postgreSQL database, the empty data directory created in the above step should be initialized using the initdb command as shown below.

# su - postgres # /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

Step 7: Validate the postgreSQL data directory

Make sure all postgres DB configuration files (For example, postgresql.conf) are created under the data directory as shown below.

$ ls -l /usr/local/pgsql/data total 60 drwx------ 7 postgres postgres    67 Jan 22 14:23 base drwx------ 2 postgres postgres  4096 Jan 22 14:25 global drwx------ 2 postgres postgres    18 Jan 22 14:23 pg_clog -rw------- 1 postgres postgres  4302 Jan 22 14:23 pg_hba.conf -rw------- 1 postgres postgres  4476 Jan 22 14:23 pg_hba.conf-default -rw------- 1 postgres postgres  1636 Jan 22 14:23 pg_ident.conf drwx------ 4 postgres postgres    36 Jan 22 14:23 pg_multixact drwx------ 2 postgres postgres    18 Jan 22 14:24 pg_notify drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_serial drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_snapshots drwx------ 2 postgres postgres    25 Jan 22 16:09 pg_stat_tmp drwx------ 2 postgres postgres    18 Jan 22 14:23 pg_subtrans drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_tblspc drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_twophase -rw------- 1 postgres postgres     4 Jan 22 14:23 PG_VERSION drwx------ 3 postgres postgres    60 Jan 22 14:23 pg_xlog -rw------- 1 postgres postgres 19691 Jan 22 14:23 postgresql.conf -rw------- 1 postgres postgres    59 Jan 22 14:24 postmaster.opts -rw------- 1 postgres postgres    70 Jan 22 14:24 postmaster.pid -rw------- 1 postgres postgres   940 Jan 22 14:23 startup.log

Step 8: Start postgreSQL database

Use the postgres postmaster command to start the postgreSQL server in the background as shown below.

$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 & [1] 2222 $ cat logfile LOG:  database system was shut down at 2020-01-22 11:10:10 WIB LOG:  database system is ready to accept connections LOG:  autovacuum launcher started

Step 9: Create postgreSQL DB and test the installation

Create a test database and connect to it to make sure the installation was successful as shown below

$ /usr/local/pgsql/bin/createdb test $ /usr/local/pgsql/bin/psql test Welcome to psql 9.2.24, the PostgreSQL interactive terminal. Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help with psql commands        \g or terminate with semicolon to execute query        \q to quit test=#

Step 10:Enable remote access to PostgreSQL server

By default, the PostgreSQL server listens only on the local interface 127.0.0.1.
To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = '*'
in the CONNECTIONS AND AUTHENTICATION section.

$ sudo vim /usr/local/pgsql/data/postgresql.conf

Edit like below

#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*'     # what IP address(es) to listen on; port = 5432

$ sudo vim /usr/local/pgsql/data/pg_hba.conf

Edit like below

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only local   all             all                                     md5 # IPv4 local connections: host    all             all             127.0.0.1/32            ident # IPv6 local connections: host    all             all             ::1/128                 ident # Allow replication connections from localhost, by a user with the # replication privilege. local   replication     all                                     peer host    replication     all             127.0.0.1/32            ident host    replication     all             ::1/128                 ident host    all             all             0.0.0.0/0               md5

save the file and restart the PostgreSQL service with:

$ sudo systemctl restart postgresql

Verify the changes with the ss utility or netstat

$ ss -nlt | grep 5432 LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:* LISTEN   0         128                    [::]:5432                [::]:*

or you can use netstat

# netstat -pltnu | grep 5432 tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      5360/postmaster

As you can see from the output above the PostgreSQL server is listening on all interfaces (0.0.0.0).

and the last open port 5432

firewall-cmd --zone=public --add-port=5422/tcp firewall-cmd --zone=public --add-port=5422/tcp --permanent systemctl restart firewalld

Value '0000-00-00' can not be represented as java.sql.Timestamp IN PENTAHO (PDI)

In Kettle (Pentaho Data Integration), when you use a "Table Input" component, you have to create a database connection.
In the database connection form, fill the fields of your mysql database, and test it.
If successfull, go to Options (left tab)
In the right tab, below defaultFetchSize and useCursorFetch, add zeroDateTimeBehavior and it's value convertToNull

Hope this helps !

How do you create a read only user in postgresql

Grant usage/select to a single table

If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:

GRANT CONNECT ON DATABASE postgres TO user1; GRANT USAGE ON SCHEMA public TO user1; GRANT SELECT ON table01 TO user1;

Multiple tables/views

you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user1;

Or you can create role before creating a user, like i usually do

-- Create a group CREATE ROLE read_access_dwh; -- Grant access to existing tables GRANT USAGE ON SCHEMA dwh TO read_access_dwh; GRANT SELECT ON ALL TABLES IN SCHEMA dwh TO read_access_dwh; -- Grant access to future tables ALTER DEFAULT PRIVILEGES IN SCHEMA dwhp GRANT SELECT ON TABLES TO read_access_dwh; -- Create a final user with password CREATE USER report_dwh WITH PASSWORD 'yourpassword'; GRANT CONNECT ON DATABASE postgres TO report_dwh; GRANT read_access_dwh TO report_dwh;

Postgres Command Line Connection from shell

This article describes how to connect to a PostgreSQL database from the shell using the psql program.
You can use the psql program as a quick and easy way to access your databases directly.


PSQL Commands: Connect to PostgreSQL from shell

To connect to PostgreSQL cli:

1. login to the server with SSH where the postgres database is located.


2. At the shell, type the following command. Replace DBNAME with the name of the database, and USERNAME with the database username:
   
   

   psql DBNAME USERNAME



3. At the Password prompt, type the database user's password. When you type the correct password, the psql prompt appears.


4. After you access a PostgreSQL database, you can run SQL queries and more. Here are some common psql commands:


• To view help for psql commands, type \?.
• To view help for SQL commands, type \h.
• To view information about the current database connection, type \conninfo.
• To list the database's tables and their respective owners, type \dt.
• To list all of the tables, views, and sequences in the database, type \z.
• To exit the psql program, type \q.