I resolved this issue using below options:

 1. Whitelist your DB host from your network team to make sure you have access to remote host  
 2. Install postgreSQL version 4 or above  
 3. Run below command:
    ```
    psql -h &lt;REMOTE HOST&gt; -p &lt;REMOTE PORT&gt; -U &lt;DB_USER&gt; &lt;DB_NAME&gt;
    ```

```
psql -h &lt;IP_Address&gt; -p &lt;port_no&gt; -d &lt;database_name&gt; -U &lt;DB_username&gt; -W
```

`-W` option will prompt for password. For example:

```
psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W
```

I figured it out.

Had to set `listen_addresses=&#39;*&#39;` in `postgresql.conf` to allow for incoming connections from any ip / all ip

Step Wise below

 1. Opening the Port - Make sure the PSQL Port is open to all remote connections or connections from a specific set of IPs as per your requirement. PSQL, in general, runs at port 5432, and it is configurable, so expose relevant Port accordingly.
 2. Update Remote Server PSQL Configuration  - Set `listen_addresses = &#39;*&#39;` in `postgresql.conf` file, path in general is `/etc/postgresql/psql_version/main/postgresql.conf`
 3. Connect remotely - `psql -U &lt;db_username&gt; -h &lt;IP_address&gt;` - in case  psql is running on a port other than 5432 on the remote server, specify port by adding `-p &lt;port_number&gt;`

A little plus below -
In case the IP has been mapped to a domain name, you can connect by replacing `&lt;IP_address&gt;` with `&lt;host_name&gt;`. To do this, add a new connection rule in `pg_hba.conf` file

Note -
All above explained can cause security issues - best practice always is to either keep your psql port closed, or only allow a list of IPs to connect through the port. 


Note that &quot;ident&quot; in pg_hba.conf requires a &quot;ident server&quot; to be running on the client.

Why does adding `&lt;/script&gt;` in a comment break the parser? Is this a bug or is there something in the documentation I&#39;ve overlooked?

I&#39;ve tested this in Chrome, Firefox, Opera, Internet&amp;nbsp;Explorer and they all produce the same result.

**Single-line comment:**

&lt;!-- begin snippet: js hide: false --&gt;

&lt;!-- language: lang-js --&gt;

    function Foo(){
      // &lt;/script&gt;
      alert(&quot;bar&quot;);
    };

    Foo();

&lt;!-- end snippet --&gt;

**Multi-line comment:**

&lt;!-- begin snippet: js hide: false --&gt;

&lt;!-- language: lang-js --&gt;

    function Foo(){
      /*
          &lt;/script&gt;
      */
      alert(&quot;bar&quot;);
    };

    Foo();

&lt;!-- end snippet --&gt;



Why does adding &lt;/script&gt; in a comment break the parser?

So, having spend many years in an object oriented world with code reuse, design patterns and best practices always taken into account, I find myself struggling somewhat with code organization and code reuse in world of Spark. 

If I try to write code in a reusable way, it nearly always comes with a performance cost and I end up rewriting it to whatever is optimal for my particular use case. This constant &quot;write what is optimal for this particular use case&quot; also affects code organization, because splitting code into different objects or modules is difficult when &quot;it all really belongs together&quot; and I thus end up with very few &quot;God&quot; object containing long chains of complex transformations. In fact, I frequently think that if I had taken a look at most of the Spark code I&#39;m writing now back when I was working in the object oriented world, I would have winced and dismissed it as &quot;spaghetti code&quot;. 

I have surfed the internet trying to find some sort of equivalent to the best practices of the object oriented world, but without much luck. I can find some &quot;best practices&quot; for functional programming but Spark just adds an extra layer, because performance is such a major factor here. 

So my question to you is, have any of you Spark gurus found some best practices for writing Spark code that you can recommend?

**EDIT**

As written in a comment, I did not actually expect anyone to post an answer on how to _solve_ this problem, but rather I was hoping that someone in this community had come across some Martin Fowler type, who had written som articles or blog posts somewhere on how to address problems with code organization in the world of Spark. 

@DanielDarabos suggested that I might put in an example of a situation where code organization and performance are conflicting. While I find that I frequently have issues with this in my everyday work, I find it a bit hard to boil it down to a good minimal example ;) but I will try.

In the object oriented world, I&#39;m a big fan of the Single Responsibility Principle, so I would make sure that my methods were only responsible for one thing. It makes them reusable and easily testable. So if I had to, say, calculate the sum of some numbers in a list (matching some criteria) and I had to calculate the average of the same number, I would most definitely create two methods - one that calculated the sum and one that calculated the average. Like this:

&lt;!-- language: scala --&gt;
    
    def main(implicit args: Array[String]): Unit = {
      val list = List((&quot;DK&quot;, 1.2), (&quot;DK&quot;, 1.4), (&quot;SE&quot;, 1.5))

      println(&quot;Summed weights for DK = &quot; + summedWeights(list, &quot;DK&quot;)
      println(&quot;Averaged weights for DK = &quot; + averagedWeights(list, &quot;DK&quot;)
    }

    def summedWeights(list: List, country: String): Double = {
      list.filter(_._1 == country).map(_._2).sum
    }

    def averagedWeights(list: List, country: String): Double = {
      val filteredByCountry = list.filter(_._1 == country) 
      filteredByCountry.map(_._2).sum/ filteredByCountry.length
    }


I can of course continue to honor SRP in Spark:


&lt;!-- language: scala --&gt;

    def main(implicit args: Array[String]): Unit = {
      val df = List((&quot;DK&quot;, 1.2), (&quot;DK&quot;, 1.4), (&quot;SE&quot;, 1.5)).toDF(&quot;country&quot;, &quot;weight&quot;)

      println(&quot;Summed weights for DK = &quot; + summedWeights(df, &quot;DK&quot;)
      println(&quot;Averaged weights for DK = &quot; + averagedWeights(df, &quot;DK&quot;)
    }


    def avgWeights(df: DataFrame, country: String, sqlContext: SQLContext): Double = {
      import org.apache.spark.sql.functions._
      import sqlContext.implicits._

      val countrySpecific = df.filter(&#39;country === country)
      val summedWeight = countrySpecific.agg(avg(&#39;weight))

      summedWeight.first().getDouble(0)
    }

    def summedWeights(df: DataFrame, country: String, sqlContext: SQLContext): Double = {
      import org.apache.spark.sql.functions._
      import sqlContext.implicits._

      val countrySpecific = df.filter(&#39;country === country)
      val summedWeight = countrySpecific.agg(sum(&#39;weight))

      summedWeight.first().getDouble(0)
    }

But because my `df` may contain billions of rows I would rather not have to perform the `filter` twice. In fact, performance is directly coupled to EMR cost, so I REALLY don&#39;t want that. To overcome it, I thus decide to violate SRP and simply put the two functions in one and make sure I call persist on the country-filtered `DataFrame`, like this:


&lt;!-- language: scala --&gt;

    def summedAndAveragedWeights(df: DataFrame, country: String, sqlContext: SQLContext): (Double, Double) = {
      import org.apache.spark.sql.functions._
      import sqlContext.implicits._

      val countrySpecific = df.filter(&#39;country === country).persist(StorageLevel.MEMORY_AND_DISK_SER)
      val summedWeights = countrySpecific.agg(sum(&#39;weight)).first().getDouble(0)
      val averagedWeights = summedWeights / countrySpecific.count()

      (summedWeights, averagedWeights)
    }

Now, this example if of course a huge simplification of what&#39;s encountered in real life. Here I could simply solve it by filtering and persisting `df` _before_ handing it to the sum and avg functions (which would also be more SRP), but in real life there may be a number of intermediate calculations going on that are needed again and again. In other words, the `filter` function here is merely an attempt to make a _simple_ example of something that will benefit from being persisted. In fact, I think calls to `persist` is a keyword here. Calling `persist` will vastly speed up my job, but the cost is that I have to tightly couple all code that depends on the persisted `DataFrame` - even if they are logically separate.   

Spark code organization and best practices

I want to remotely connect to a Postgres instance. I know we can do this using the psql command passing the hostname

I tried the following:

    psql -U postgres -p 5432 -h hostname

I modified the /etc/postgresql/9.3/main/pg_hba.conf file on the target machine to allow remote connections by default

I added the following line to the file 

    host all all source_ip/32 trust

I restarted the cluster using 

    pg_ctlcluster 9.2 mycluster stop
    pg_ctlcluster 9.2 mycluster start


However, when I try to connect from the source_ip, I still get the error 

&gt; Is the server running on host &quot;&quot; and accepting TCP/IP connections on port 5432?

What am I doing wrong here?


PostgreSQL: Remotely connecting to Postgres instance using psql command

I want to remotely connect to a Postgres instance. I know we can do this using the psql command passing the hostname
I tried the following:
<pre><code class="hljs language-css">psql -U postgres -p 5432 -h hostname
</code></pre>
I modified the /etc/postgresql/9.3/main/pg_hba.conf file on the target machine to allow remote connections by default
I added the following line to the file
<pre><code class="hljs language-css">host all all source_ip/32 trust
</code></pre>
I restarted the cluster using
<pre><code class="hljs language-arduino">pg_ctlcluster 9.2 mycluster stop
pg_ctlcluster 9.2 mycluster start
</code></pre>
However, when I try to connect from the source_ip, I still get the error
> Is the server running on host "" and accepting TCP/IP connections on port 5432?
What am I doing wrong here?

HikariCP - connection is not available

I installed postgres using homebrew.
I would like to locate he files pg_hba.conf and postgresql.conf but I cannot find them.

I followed this :

https://askubuntu.com/questions/256534/how-do-i-find-the-path-to-pg-hba-conf-from-the-shell

and this 

http://www.kelvinwong.ca/tag/pg_hba-conf/

but I still cannot find it.

Cannot find pg_hba.conf and postgreql.conf file on OS X?

I&#39;m running the latest version of PostgreSQL `9.4.5-1.pgdg14.04+1`, and am attempting to calculate the length of a JSONB array using the `JSON_ARRAY_LENGTH` function as described in the [PostgreSQL 9.4 Documentation][1]

Here is the exact query I&#39;m attempting to run:

    SELECT JSON_ARRAY_LENGTH(&#39;[&quot;hi&quot;,&quot;ho&quot;]&#39;::jsonb) AS length

When I run that query, I would expect to be returned a value of `2`, but instead am encountering the error: `ERROR: function json_array_length(jsonb) does not exist`

Am I missing something very obvious in the documentation? It specifically states you may call `JSON_ARRAY_LENGTH` passing either a `json` or `jsonb` data-type. I&#39;m explicitly casting to `jsonb` so I&#39;m at a bit of a loss.
 
Has anyone else encountered this problem, or would someone point out what I&#39;m doing wrong here?


**UPDATE: I Mis-Read The Documentation**

I should have been calling `JSONB_ARRAY_LENGTH`, not `JSON_ARRAY_LENGTH`. Notice the &quot;B&quot; after &quot;JSON&quot;. Thanks guys.

  [1]: http://www.postgresql.org/docs/9.4/static/functions-json.html

Calculate JSONB Array Length Using PostgreSQL 9.4

I would like to store an array of floating point values with Ecto using Postgres.
I&#39;m using Ecto with the Phoenix Framework and Elixir.

How would I define my model and migration for this?

I haven&#39;t tried much, except searching the web, which didn&#39;t find anything useful :-(

I did try defining a model with a schema like this:

      schema &quot;my_model&quot; do
        field :my_array, :array
    
        timestamps
      end

which gave an error &quot;invalid or unknown type :array for field :my_array&quot;


How to store array with Ecto using Postgres

I am coming from MySQL, and in MySQL you can use AUTOINCREMENT for a row&#39;s unique id as the primary key.

I find that there is no AUTOINCREMENT in Postgresql, only SEQUENCE or UUID.I have read somewhere that we can use UUID as the primary key of a table. This has the added advantage of masking other user&#39;s id (as I want to build APIs that take the ID in as a parameter). Which should I use for Postgresql?

UUID or SEQUENCE for primary key?

I have a postgres database with multiple schemas. When I connect to the database from a shell with `psql` and I run `\dt` it uses the default connection schema which is *public*. Is there a flag I can specify or how can I change the schema?

How to select a schema in postgres when using psql?

Given a file with a SQL insert:

    INSERT INTO countries (id, country_code, name)
    VALUES
        (1, &#39;AF&#39;, &#39;Afghanistan&#39;),
        (2, &#39;AL&#39;, &#39;Albania&#39;);

I would like to run the file by using the `docker run` command on a container that is running postgres.

I&#39;ve tried this:

    docker run -e domain=&quot;192.168.99.100&quot; pg /bin/bash -c &quot;psql -d whiteboard_api -a -f inserts_into_countries_table.sql&quot;
    psql: could not connect to server: Connection refused
    	Is the server running locally and accepting
    	connections on Unix domain socket &quot;/var/run/postgresql/.s.PGSQL.5432&quot;?

you can see my image is pg:

    capistrano:whiteboard_v2 jzollars$ docker ps
    CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
    6b500bec9210        bbb                 &quot;/usr/bin/supervisord&quot;   4 weeks ago         Up 4 weeks          0.0.0.0:80-&gt;80/tcp       distracted_raman
    c1e88f2695f5        wh                  &quot;/usr/bin/supervisord&quot;   4 weeks ago         Up 4 weeks          0.0.0.0:3000-&gt;3000/tcp   high_einstein
    7e383e99bdc3        pg                  &quot;/usr/lib/postgresql/&quot;   4 weeks ago         Up 4 weeks          0.0.0.0:5432-&gt;5432/tcp   pg_test

How can I load this file and run it in a docker container using `docker run`?

How do I run a sql file of inserts through docker run?

I installed PostgreSQL via the graphical install on [http://www.postgresql.org/download/macosx/][1]

I see it in my applications and also have the psql terminal in my applications. I need psql to work in the regular terminal for another bash script I&#39;m running for an app.

For some reason, when I run

    psql

in the Mac terminal, my output is 

    -bash: psql: command not found

I ran the following in the terminal:

    locate psql | grep /bin

and the output was

    /Library/PostgreSQL/9.5/bin/psql

I then edited my ~/.bash_profile and added it to the path like so:

    export PATH = /Library/PostgreSQL/9.5/bin/psql:$PATH

The only other thing in ~/.bash_profile is SDK man and it&#39;s at the bottom of the script as it says it should be. I&#39;ve tried setting the bath to just the /Library/PostgreSQL/9.5/bin/ as well. I&#39;ve restarted my terminal also.

How can I get psql to work? 

**EDIT**
After adding to .bashrc, this output is returned when I open terminal

    -bash: export: `/Library/PostgreSQL/9.5/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin&#39;: not a valid identifier


  [1]: http://www.postgresql.org/download/macosx/

psql: command not found Mac

Could you tell me how to check what indexes are created for some table in postgresql ?  

How to list indexes created for table in postgres

When using Psql in Linux, if the result of my SQL query contains many columns or long strings of data, it will wrap the initial view and only once I scroll to the side will it stop wrapping and show each row on a separate line.

I&#39;ve tried various [`\pset` options][1] such as `format unaligned`, `format aligned`, `format wrapped`, `columns 0`, `columns 1000`, but none seemed to *fully* stop wrapping unless I generate static output to a file.

How can I set it to never wrap the output while still being scrollable and showing the result using the default ascii table format?


  [1]: https://www.postgresql.org/docs/9.2/static/app-psql.html

Content Type	Original Author	Original Content on Stackoverflow
Question	Hardik Kamdar	View Question on Stackoverflow
Solution 1 - Postgresql	Jagadish S	View Answer on Stackoverflow
Solution 2 - Postgresql	devops-admin	View Answer on Stackoverflow
Solution 3 - Postgresql	Hardik Kamdar	View Answer on Stackoverflow
Solution 4 - Postgresql	Draeger	View Answer on Stackoverflow
Solution 5 - Postgresql	Richard T	View Answer on Stackoverflow

PostgreSQL: Remotely connecting to Postgres instance using psql command

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Solution 3 - Postgresql

Solution 4 - Postgresql

Solution 5 - Postgresql

Spark code organization and best practices

Why does adding </script> in a comment break the parser?

Attributions