Useful Postgresql commands/sqls for beginners

The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running
SELECT pg_is_in_recovery();
The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;
The current transaction has been started with. You can find out if that is the case using the undocumented parameter
SHOW transaction_read_only;
where is pgdata?
SHOW data_directory;
Replication user is active?
select * from pg_stat_activity where usename = 'replicator';

PSQL Listing Commands


\x on; --extended display
\c db; --connect to database
\du+; --list users
\dt+ schema.tablename; --lists tables
\df+ schema.func/procname;
\d+  schema.tablename; -- gives description of table

Minimal Postgresql 11 Replication on Ubuntu 18.04

I have two Virtual Machines/Servers with Ubuntu 18.04 installed on it. On both these machines I have Postgresql 11 installed. Let us say the IP of master is 127.10.10.1 and IP of slave is 127.10.10.2

Changes to Master

Firstly we shutdown master

sudo service postgresql stop

Go to psql as postgres and create a replication user called replicator.

CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'replica'; 

Go to pg_hba.conf file which is in /etc/postgresql/11/main folder

add replication user with slave ip

#add replication user with slave ip
host replication replicator 127.10.10.2/32 md5

Go to postgresql.conf file which is in /etc/postgresql/11/main folder

#change listen_address to allow all ips with  
# listen_addresses = '*'
# OR mention specifically the slave ip
listen_addresses = 'localhost, 127.10.10.2'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Start pg master

sudo service postgresql start

Slave Configuration

Stop the slave

sudo service postgresql stop

Go to pg_hba.conf file which is in /etc/postgresql/11/main folder

add replication user with master ip

#add replication user with master ip
host replication replicator 127.10.10.1/32 md5

Go to postgresql.conf file which is in /etc/postgresql/11/main folder

#change listen_address to allow all ips with  
# listen_addresses = '*'
# OR mention specifically the slave ip
listen_addresses = 'localhost, 127.10.10.2'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

One more very important part of the postgresql.conf to do is set the slave in standby mode. That way it is in “read only” and just replicates events that happen in the master.

standby_mode = 'on'

Now we need to do a “first” copy of data from the master to the slave. Which implies we need to delete all data in postgresql on the slave and take everything thats on the master. Now first let’s find out where the postgresql data (PGDATA) is .

-- login as user postgres
$ sudo -i -u postgres
--login to psql
$psql
--to find out where pgdata is:
$show data_directory;
data_directory
/var/lib/postgresql/11/main
(1 row)

Now we go to that folder and make a backup and remove main. Please note that one has to be logged in as user postgres in order to access main folder

$mv main main_bkup
$rm -rf main

Here comes the real deal now. We have to connect to the master and take the master’s pgdata and move it to slave. There’s one elegant command for that:

$ pg_basebackup -h master-ip -D /var/lib/postgresql/11/main/ -P -U  replicator --wal-method=stream
-- when this run, the following output is printed
Password:
waiting for checkpoint
0/46100 kB (0%), 0/1 tablespace
17829/46100 kB (38%), 0/1 tablespace
46109/46109 kB (100%), 0/1 tablespace
46109/46109 kB (100%), 1/1 tablespace

Now we go inside the main folder and create a recovery.conf file. This too has to be done as postgres user.

$ cd /var/lib/postgresql/11/main/
$ touch recovery.conf

The contents of recovery.conf are

standby_mode         = 'on'
primary_conninfo = 'host=master-ip port=5432 user=replicator password=my_replicator_password'
trigger_file = '/tmp/MasterNow'

Start pg slave

sudo service postgresql start

Testing Replication

On the master, see if the replication user shows activity

select * from pg_stat_activity where usename = 'replicator';
--gives the following output
-[ RECORD 1 ]----+------------------------------
datid |
datname |
pid | 27253
usesysid | 16597
usename | replicator
application_name | walreceiver
client_addr | 127.10.10.2
client_hostname |
client_port | 42290
backend_start | 2019-07-03 09:15:13.939534+02
xact_start |
query_start |
state_change | 2019-07-03 09:15:13.948509+02
wait_event_type | Activity
wait_event | WalSenderMain
state | active
backend_xid |
backend_xmin |
query |
backend_type | walsender

You can now create a table in master add rows to it

--in psql from master--
create table test (msg varchar(255), created_on date);
insert into test values ('test1' , current_date);

We can now select these values from the slave.

--in psql from slave --
select * from test;
-[ RECORD 1 ]-----
msg | test1
datum | 2019-07-03

When we try to insert in the slave then we get error

insert into test values ('hari', current_date);
ERROR: cannot execute INSERT in a read-only transaction

This is because the slave is in standby mode. It can only be replicated from master. You can check these values with following commands:

The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running
SELECT pg_is_in_recovery();
--The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;

Happy replicating!! At this point i would also like to thank the innumerable blogs of other great techies that put out this information out there.

Installing oracle_fdw in Postgresql-11 on Ubuntu18.04

To install the oracle foreign data wrapper on Postgres, firstly we need to make sure we are able to connect to Oracle from our Ubuntu machine.

For this, make sure that all the Oracle components are installed. I followed the beautiful step by step instructions given here:
https://mikesmithers.wordpress.com/2011/04/03/oracle-instant-client-on-ubuntu-with-added-aliens/

After that, test your oracle connection using this sqlplus format

sqlplus oraschemaname/oraschemapassword@//hostname:1521/Service

e.g sqlplus xx/yy@//abc.def.com:1521/SERVICE

If you have connected succesfully to Oracle then we can now get busy with the installation of oracle_fdw

I downloaded the fdw from here: https://github.com/laurenz/oracle_fdw/releases/

Naturally all credit for everything that I documented here goes to this person who created the oracle_fdw. I am only trying to document my experience of installing and what difficulties i faced.

After downloading the zip file oracle_fdw-ORACLE_FDW_2_1_0.zip, i unzipped it into a folder with the same name oracle_fdw-ORACLE_FDW_2_1_0

The instructions say that one goes into the folder in command prompt and then types make.

/home/ana/Downloads/oracle_fdw-ORACLE_FDW_2_1_0$ make

You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application.
make: *** No targets. Stop.

So when you see this, you need to install some missing components of postgres. So we run the following installation:

sudo apt-get install postgresql-server-dev-11

Now rerun make, and it will get all the source files ready to be recompiled.

After the make, one has to install oracle_fdw with $make install

But in order for the installation to work, certain places where postgres is installed should get a write access. When they have a write access then, the installation of the fdw will be a success.

 ana@ana-pg % sudo chmod a+rwx /usr/share/doc
ana@ana-pg % sudo chmod a+rwx /usr/lib/
ana@ana-pg % sudo chmod a+rwx /usr/lib/postgresql/11/lib/
ana@ana-pg % sudo chmod a+rwx /usr/share/postgresql/11/extension/
ana@ana-pg % sudo chmod a+rwx /usr/lib/postgresql/11/lib/bitcode/

Now we run make install

ana@ana-pg % make install
/bin/mkdir -p '/usr/lib/postgresql/11/lib'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-11/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/usr/lib/postgresql/11/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/postgresql-doc-11/extension/'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode/oracle_fdw'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/
/usr/bin/install -c -m 644 oracle_fdw.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_utils.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_gis.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o oracle_fdw.index.bc oracle_fdw/oracle_fdw.bc oracle_fdw/oracle_utils.bc oracle_fdw/oracle_gis.bc

Now we can create an extension in the test database of postgresql-11. For that we login again into psql

ana@ana-pg ~ % sudo -i -u postgres
[sudo] password for ana: 
postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# \c testdb;

Now we create an extension using the following:

testdb=# create extension oracle_fdw;
CREATE EXTENSION

To check if postgres has installed the oracle_fdw successfully, log into psql and type

testdb=# select oracle_diag();

oracle_diag
oracle_fdw 2.1.0, PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1), Oracle client 12.2.0.1.0
(1 row)

After installing oracle_fdw, we now get hands on and access a table in the oracle schema.

Back to firststeps after installing postgres Import Oracle Foreign Server

First Steps after installing Postgres-11 on Ubuntu 18.04

After installation we need to login to postgresql to create database, roles, schemas etc

When postgresql11 is installed, there is a default superuser postgres created, which does not have any password. We just login to the database using this superuser

sudo -i -u postgres

Now we can enter into psql, which is the database commandline

postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.

Now in the command line of psql, we create a database

create database testdb;

To see the list of databases installed:

\list

To quit psql type

\q

Now we create another superuser other than postgres

postgres@ana-pg:~$ createuser --interactive
Enter name of role to add: rtam
Shall the new role be a superuser? (y/n) y
postgres@ana-pg:~$ alter user rtam with password 'partha';

With this new superuser rtam one can add a new server in pgAdmin4. Here we just need to click on “Add New Server”. Then name the server anything. Under Connections Tab, hostname is localhost. 

The username and password are that of the new user we created rtam

The Add New Server will not work with user as postgres, because postgres is created by default in Ubuntu without a password. Now one can see in pgAdmin4 that the database testdb has been created.

back to installing postgres          Installing oracle_fdw

Installing Postgresql-11 on Ubuntu 18.04

Steps involved in installing Postgresql-11 on Ubuntu 18.04.02 are:

We get the package to be installed from the APT repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update

You might get an error message, about missing public key, that looks like this: The following signatures couldn’t be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8

Solution:

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 7FCC7D46ACCC4CF8
sudo apt-get update

Now we can install Postgresql Database and the pgadmin tool that is a graphical tool for database administration.

sudo apt-get install postgresql-11 pgadmin4

Now we setup the database -> First Steps to Setup Database

Install Weblogic Server locally on Windows desktop PC

I am going to demonstrate with Weblogic 12.1.3 and JDK 161.

In order to get Weblogic working, we have to have JDK installed, so let’s get that out of the way. JDK 9 had compatability problems, so I used JDK 8 u 161.

Remember that once JDK is installed, you need to set the JAVA_HOME parameter. This is under environment variables.

For e.g. you set JAVA_HOME to C:\Program Files\Java\jdk1.8.0_161

When you are in command prompt, you need to verify two things:

1. c:\ java -version will give you an output like

java version “1.8.0_161”
Java(TM) SE Runtime Environment (build 1.8.0_161-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)

If this is not the case, we need to check if the “path” parameter under system variables has this entry C:\ProgramData\Oracle\Java\javapath

You might need to restart your computer if this is not showing up.

2. At command prompt you type echo %JAVA_HOME% and this gives you the folder path C:\Program Files\Java\jdk1.8.0_161

You might need to restart your computer if this is not showing up.

Now we come to the actual installation of Weblogic Server.

1. Extract the contents of the zip file into a directory (recommeded directory is c:/home/myhome/mywls/wls12130).

2. Set the MW_HOME parameter under system variables to c:/home/myhome/mywls/wls12130. You can test if this is set by checking on command prompt

>echo %MW_HOME%

You might need to restart your computer if this is not showing up.

3. Run the installation configuration script in the MW_HOME directory.

i.e. in CMD prompt, under MW_HOME directory execute

> configure.cmd -silent

4. Setup WLS environment in the current shell

> %MW_HOME%\wlserver\server\bin\setWLSEnv.cmd

5. It is recommended that we create domains outside the MW_HOME

 

>mkdir C:\home\myhome\mydomain

>cd C:\home\myhome\mydomain

Create the domain by invoking the GUI configuration
wizard (Run MW_HOME/wlserver/common/bin/config.cmd). The GUI will ask you to create an Admin User and Password.

6. Start a browser and open up url – ‘http://localhost:7001/console’ to
administer the server using the Admin User and Password. The default Admin User is “weblogic”

Please note, that in the weblogic server C:\home\myhome\mywls\wls12130\README_WIN.txt is a good readme file. I have obviously followed the steps mentioned there.

Multidomains are basically synonyms

So I have a website with a DomainserverPlan from a webhosting company. Let us say it is called www.abc.com. Under this I can install Content Management Systems like WordPress etc.

Now I can buy Domainonly. Which is I buy a url www.xyz.com.This doesn’t come with a server, hence I need to reorder it under my Domainserver plan.

Hence, I reorder www.xyz.com under www.abc.com under the root (/) folder. Which basically means that whether I type www.abc.com or www.xyz.com, the same content will show up, which is available on the index.php page.

In other words the two urls are interchangeable.

What i then do is that since the content of www.xyz.com is different from www.abc.com, both of them will have their data in two separate folder /xyz and /abc.

I then modify the .htaccess so that the when url is www.abc.com, it is redirected to www.abc.com/abc/ folder & when url is www.xyz.com, it is redirected to www.abc.com/xyz/ (or www.xyz.com/xyz/).

Below is the .htaccess code:

RewriteEngine On
RewriteCond %{HTTP_HOST} ^(www.)?abc.com$
RewriteRule ^(/)?$ abc [L]

RewriteCond %{HTTP_HOST} ^(www.)?xyz.com$
RewriteRule ^(/)?$ http://www.xyz.com/xyz [L]

 

 

Classpath definition for command line execution of Java Programs

I was trying to execute a really simple program in my Intellij IDE:

package com.example;

public class TestCLI {

    public static void main(String args[]) {
        for (int i = 0; i < args.length; i++) {
            System.out.println("args[" + i + "]: " +
                    args[i]);
        }
    }

}

Executing this program with
java TestCLI anti dote
We will see the following output:
args[0]: anti
args[1]: dote

My problem with the command line, was that it was constantly throwing an error at me:

Fehler: Hauptklasse com.example.TestCLI konnte nicht gefunden oder geladen werden

Error: Could not find or load main class com.example.TestCLI

The problem is that Intellij stores the complied classes (.class files) in another “out” directory, whereas the .java files stay in the “src” directory.

So then I had to rewrite the java command as

java -classpath C:\Users\XXXXX\IdeaProjects\Examples\out\production\Examples com.example.TestCLI anti dote

Here it is interesting to note that the folder “Examples” is the Project folder. Which further divides itself into src and out folders. The classpath has to refer to out\production\Examples.

The actual path of the class com\example\TestCLI.class has to be rewritten as com.example.TestCLI and then the parameters “anti” and “dote” are added.

Custom Rom Xperia Z5 E6653

Here’s the thing. Installing a custom rom on a Sony phone is painful. Really painful. Especially the Z5 is badly supported. I intend to only provide an overview here. The Internet will give you all the details. You just need to google the right key words.

Here’s how to go about it:

On your phone:

  1. Under Settings>Security set Unknown Sources to true.
  2. Enable USB Debugging under Developer Options. If you are not a developer, then click on the build number several times to enable Developer Options.

On your computer(Windows):

  1. Download and install Minimal ADB and Fastboot. The other variant is to have android studio installed. What does all this mean? ADB stands for Android Debug Bridge. You can push (“flash”) software from your computer into the phone, when you have ADB and Fastboot installed.
  2. Check if this works – How? Open the cmd line application and then type “adb devices”. You should see a alphanumeric number with the word device.
  3. You need to also download a driver, called a fastboot driver “S1 Fastboot Driver” or “ADB Interface Driver” and install it. Now how do you install it? This isn’t really a piece of cake. On the cmd prompt type “adb reboot-bootloader”. You will enter into the fastboot mode on the phone, with the blue led light. On your computer under “Device Manager” you will see that you have a new device (under “Other Devices”) with an exclamation mark. You need to then chose to install the driver. Select the driver “ADB Interface”.
  4. Once the driver is installed, when you type “fastboot devices” you will see the same thing, when you typed “adb devices” when the device was on.

Now you need to download certain softwares: We need a way to break into the kernel and install TWRP Recovery. When you have recovery installed you can then install the custom roms.

So download:

  1. E6653 Kernel boot image. –> Save on Minimal ADB Fastboot folder
  2. TWRP for E6653 ( I use 3.1.1) –> Save on Minimal ADB Fastboot folder
  3. Lineage OS –> Save on phone SD Card
  4. Gapps (arm 64) –> Save on phone SD Card

 

Now we have to load through fastboot the kernel and recovery:

  1. Go to command line. (Your phone is in fastboot mode -> switched off with blue light on)
  2. type in “fastboot flash boot boot.img” so that you can install the kernel/boot
  3. type in “fastboot flash recovery recovery.img” so that you can install twrp
  4. then type “fastboot reboot”
  5. When the led turns yellow, press the volume up button. You will then enter TWRP.
  6. If you are unable to enter TWRP then its mostly because of a shitty version of the kernel or twrp that you flashed. Since there are so many places, where you could possible download this stuff.. and several versions..its hard to say ( at least I am not technically capable of pinpointing the error)
  7. In TWRP install the two zip files – first lineageos then gapps.
  8. That’s it. Reboot.

If you want to now Root your LineageOS Sony Xperia z5 E6653, then you need to save SuperSU.zip file (v2.79 is compatible with LineageOS 14.1) on SD Card. Then you can go into TWRP.

How to get into recovery mode? In Developer Options, set “Advanced Restart” to true. Then when you press the power button and select restart, you will get options to go into Recovery or Bootloader etc. Then you can go into recovery.

Once in recovery, we just need to install the supersu_Vx.xx.zip file. The phone might boot a couple of times before it finally loads. Download the apps SuperSU and Rootchecker from playstore and run them.

If you are unable to go to recovery, then its again a problem with your TWRP version/Compatibility. So go back to fastboot and flash another version and try your luck.

That’s about it. It’s been a nerve wracking two days to get this far.

Please note, that Sony’s camera and audio quality are LOST when you install Lineage OS. Your Speakers will shrill loudly and your camera might be taking too bright photos. But now you have your freedom. Is the choice worthwhile? Only time will tell.