814 reads

Top 12 FAQs About PostgreSQL

by KiranSeptember 13th, 2020

Too Long; Didn't Read

Postgres is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, and OS X. It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. Truemark Technology answers the most frequently asked questions about Postgres and the solution works perfectly.

Companies Mentioned

Coin Mentioned

featured image - Top 12 FAQs About PostgreSQL

PostgreSQL is an open-source, object-relational database management
system (ORDBMS) available for all major platforms including Linux, UNIX,
Windows, and OS X. It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server.

As developers, we have answered the most frequently asked questions
about PostgreSQL and also have confirmed that the solution works
perfectly. So, here is the list for PostgreSQL questions and answers.

Rundown For The 12 Most Asked PostgreSQL Questions

Below are the frequently asked questions about PostgreSQL.

1. How to perform “DESCRIBE TABLE” in PostgreSQL?

Answer:

You can try this (in the

psql

command-line tool):

<code class="  language-sql">\d<span class="token operator">+</span> tablename</code>

Also,

In addition to the PostgreSQL way (\d ‘something’ or \dt ‘table’ or \ds ‘sequence’ and so on)

The SQL standard way, as shown here:

<code class="  language-sql"><span class="token keyword">select</span> column_name<span class="token punctuation">,</span> data_type<span class="token punctuation">,</span> character_maximum_length
<span class="token keyword">from</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">COLUMNS</span> <span class="token keyword">where</span> table_name <span class="token operator">=</span> <span class="token string">'&lt;name of table&gt;'</span><span class="token punctuation">;</span></code>

It’s supported by many DB engines.

2. How to switch databases in PostgreSQL?

Answer:

In PostgreSQL, you can use the

\connect

meta-command of the client tool psql:

<code class="  language-sql">\<span class="token keyword">connect</span> DBNAME</code>

or in short:

<code class="  language-sql">\c DBNAME</code>

Also, you can select the database when connecting with psql. This is handy when using it from a script:

<code class="  language-sql">sudo <span class="token operator">-</span>u postgres psql <span class="token operator">-</span>c <span class="token string">"CREATE SCHEMA test AUTHORIZATION test;"</span> test</code>

3. Which version of PostgreSQL are we running?

Answer:

To check the current running version of PostgreSQL run this query from PostgreSQL:

<code class="  language-sql"><span class="token keyword">SELECT</span> version<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code>

Also, you can use the below version to check the server version and client version of PostgreSQL

Server version:

<code class=" language-sh">pg_config --version</code>

Client version:

<code class=" language-sh">psql --version</code>

You can also use this method

Using CLI:

Server version:

<code class="  language-sql">$ postgres <span class="token operator">-</span>V  <span class="token comment"># Or --version.  Use "locate bin/postgres" if not found.</span>
postgres <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.6</span><span class="token number">.1</span>
$ postgres <span class="token operator">-</span>V <span class="token operator">|</span> awk <span class="token string">'{print $NF}'</span>  <span class="token comment"># Last column is version.</span>
<span class="token number">9.6</span><span class="token number">.1</span>
$ postgres <span class="token operator">-</span>V <span class="token operator">|</span> egrep <span class="token operator">-</span>o <span class="token string">'[0-9]{1,}\.[0-9]{1,}'</span>  <span class="token comment"># Major.Minor version</span>
<span class="token number">9.6</span></code>

If having more than one installation of PostgreSQL, or if getting the “

postgres: command not found

” error:

<code class="  language-sql">$ locate bin<span class="token operator">/</span>postgres <span class="token operator">|</span> xargs <span class="token operator">-</span>i xargs <span class="token operator">-</span>t <span class="token string">'{}'</span> <span class="token operator">-</span>V  <span class="token comment"># xargs is intentionally twice.</span>
<span class="token operator">/</span>usr<span class="token operator">/</span>pgsql<span class="token operator">-</span><span class="token number">9.3</span><span class="token operator">/</span>bin<span class="token operator">/</span>postgres <span class="token operator">-</span>V 
postgres <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.3</span><span class="token number">.5</span>
<span class="token operator">/</span>usr<span class="token operator">/</span>pgsql<span class="token operator">-</span><span class="token number">9.6</span><span class="token operator">/</span>bin<span class="token operator">/</span>postgres <span class="token operator">-</span>V 
postgres <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.6</span><span class="token number">.1</span></code>

locate

doesn’t help, try

find

<code class="  language-sql">$ sudo find <span class="token operator">/</span> <span class="token operator">-</span>wholename <span class="token string">'*/bin/postgres'</span> <span class="token number">2</span><span class="token operator">&gt;</span><span class="token operator">&amp;</span><span class="token operator">-</span> <span class="token operator">|</span> xargs <span class="token operator">-</span>i xargs <span class="token operator">-</span>t <span class="token string">'{}'</span> <span class="token operator">-</span>V  <span class="token comment"># xargs is intentionally twice.</span>
<span class="token operator">/</span>usr<span class="token operator">/</span>pgsql<span class="token operator">-</span><span class="token number">9.6</span><span class="token operator">/</span>bin<span class="token operator">/</span>postgres <span class="token operator">-</span>V 
postgres <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.6</span><span class="token number">.1</span></code>

Although

postmaster

can also be used instead of

postgres

, using

postgres

is preferable because

postmaster

is a deprecated alias of

postgres

Client version:

As relevant, login as

postgres

<code class="  language-sql">$ psql <span class="token operator">-</span>V  <span class="token comment"># Or --version</span>
psql <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.6</span><span class="token number">.1</span></code>

If having more than one installation of PostgreSQL:

<code class="  language-sql">$ locate bin<span class="token operator">/</span>psql <span class="token operator">|</span> xargs <span class="token operator">-</span>i xargs <span class="token operator">-</span>t <span class="token string">'{}'</span> <span class="token operator">-</span>V  <span class="token comment"># xargs is intentionally twice.</span>
<span class="token operator">/</span>usr<span class="token operator">/</span>bin<span class="token operator">/</span>psql <span class="token operator">-</span>V 
psql <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.3</span><span class="token number">.5</span>
<span class="token operator">/</span>usr<span class="token operator">/</span>pgsql<span class="token operator">-</span><span class="token number">9.2</span><span class="token operator">/</span>bin<span class="token operator">/</span>psql <span class="token operator">-</span>V 
psql <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.2</span><span class="token number">.9</span>
<span class="token operator">/</span>usr<span class="token operator">/</span>pgsql<span class="token operator">-</span><span class="token number">9.3</span><span class="token operator">/</span>bin<span class="token operator">/</span>psql <span class="token operator">-</span>V 
psql <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.3</span><span class="token number">.5</span></code>

Using SQL:

Server version:

<code class="  language-sql"><span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> version<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                                                   version                                                    
<span class="token comment">--------------------------------------------------------------------------------------------------------------</span>
 PostgreSQL <span class="token number">9.2</span><span class="token number">.9</span> <span class="token keyword">on</span> x86_64<span class="token operator">-</span>unknown<span class="token operator">-</span>linux<span class="token operator">-</span>gnu<span class="token punctuation">,</span> compiled <span class="token keyword">by</span> gcc <span class="token punctuation">(</span>GCC<span class="token punctuation">)</span> <span class="token number">4.4</span><span class="token number">.7</span> <span class="token number">20120313</span> <span class="token punctuation">(</span>Red Hat <span class="token number">4.4</span><span class="token number">.7</span><span class="token operator">-</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">64</span><span class="token operator">-</span><span class="token keyword">bit</span>

<span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token keyword">SHOW</span> server_version<span class="token punctuation">;</span>
 server_version 
<span class="token comment">----------------</span>
 <span class="token number">9.2</span><span class="token number">.9</span>

<span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token keyword">SHOW</span> server_version_num<span class="token punctuation">;</span>
 server_version_num 
<span class="token comment">--------------------</span>
 <span class="token number">90209</span></code>

If more curious, try

=> SHOW all

Client version:

For what it’s worth, a shell command can be executed within

psql

to show the client version of the

psql

executable in the path. Note that the running

psql

can potentially be different from the one in the path.

<code class="  language-sql"><span class="token operator">=</span><span class="token operator">&gt;</span> \<span class="token operator">!</span> psql <span class="token operator">-</span>V
psql <span class="token punctuation">(</span>PostgreSQL<span class="token punctuation">)</span> <span class="token number">9.2</span><span class="token number">.9</span></code>

4. How to drop all the tables in a PostgreSQL database?

Answer:

If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is

public

<code class="  language-sql"><span class="token keyword">DROP</span> <span class="token keyword">SCHEMA</span> <span class="token keyword">public</span> <span class="token keyword">CASCADE</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">SCHEMA</span> <span class="token keyword">public</span><span class="token punctuation">;</span></code>

If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.

<code class="  language-sql"><span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> <span class="token keyword">SCHEMA</span> <span class="token keyword">public</span> <span class="token keyword">TO</span> postgres<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> <span class="token keyword">SCHEMA</span> <span class="token keyword">public</span> <span class="token keyword">TO</span> <span class="token keyword">public</span><span class="token punctuation">;</span></code>

Another way:

You can write a query to generate a SQL script like this:

<code class="  language-sql"><span class="token keyword">select</span> <span class="token string">'drop table "'</span> <span class="token operator">||</span> tablename <span class="token operator">||</span> <span class="token string">'" cascade;'</span> <span class="token keyword">from</span> pg_tables<span class="token punctuation">;</span></code>

Or:

<code class="  language-sql"><span class="token keyword">select</span> <span class="token string">'drop table if exists "'</span> <span class="token operator">||</span> tablename <span class="token operator">||</span> <span class="token string">'" cascade;'</span> <span class="token keyword">from</span> pg_tables<span class="token punctuation">;</span></code>

In case some tables are automatically dropped due to the cascade option in a previous sentence.

Additionally, you might want to filter the tables you want to drop by schema name:

<code class="  language-sql"><span class="token keyword">select</span> <span class="token string">'drop table if exists "'</span> <span class="token operator">||</span> tablename <span class="token operator">||</span> <span class="token string">'" cascade;'</span> 
  <span class="token keyword">from</span> pg_tables
 <span class="token keyword">where</span> schemaname <span class="token operator">=</span> <span class="token string">'public'</span><span class="token punctuation">;</span> <span class="token comment">-- or any other schema</span></code>

And then run it.

5. How to start the PostgreSQL server on Mac OS X?

Answer:

The Homebrew package manager includes launchctl plists to start automatically. For more information run

brew info postgres

Start manually:

pg_ctl -D /usr/local/var/postgres start

Stop manually:

pg_ctl -D /usr/local/var/postgres stop

Start automatically:

“To have launchd start PostgreSQL now and restart at login:”

brew services start postgresql

What is the result of

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

What is the result of

pg_ctl -D /usr/local/var/postgres status

Are there any error messages in the server.log?

Make sure TCP localhost connections are enabled in pg_hba.conf:

<code class="  language-sql"><span class="token comment"># IPv4 local connections:</span>
host    <span class="token keyword">all</span>             <span class="token keyword">all</span>             <span class="token number">127.0</span><span class="token number">.0</span><span class="token number">.1</span><span class="token operator">/</span><span class="token number">32</span>            trust</code>

Check the listen_addresses and port in postgresql.conf:

egrep 'listen|port' /usr/local/var/postgres/postgresql.conf

<code class="  language-sql"><span class="token comment">#listen_addresses = 'localhost'     # what IP address(es) to listen on;</span>
<span class="token comment">#port = 5432                # (change requires restart)</span></code>

Cleaning up Postgres was most likely installed via Homebrew, Fink, MacPorts, or the EnterpriseDB installer.

Check the output of the following commands to determine which package manager it was installed with:

<code class="  language-sql">brew <span class="token operator">&amp;&amp;</span> brew list<span class="token operator">|</span>grep postgres
fink <span class="token operator">&amp;&amp;</span> fink list<span class="token operator">|</span>grep postgres
port <span class="token operator">&amp;&amp;</span> port installed<span class="token operator">|</span>grep postgres</code>

Also if you want to manually start and stop PostgreSQL (installed via homebrew), the easiest way is:

<code class="  language-sql">brew services <span class="token keyword">start</span> postgresql</code>

and

<code class="  language-sql">brew services stop postgresql</code>

If you have a specific version, make sure to suffix the version, for example:

<code class="  language-sql">brew services <span class="token keyword">start</span> postgresql<span class="token variable">@10</span></code>

6. How to change the PostgreSQL user password?

Answer:

For passwordless login:

<code class="">sudo -u user_name psql db_name</code>

To reset the password if you have forgotten:

<code class="">ALTER USER user_name WITH PASSWORD 'new_password';</code>

Option2

Type:

<code class="">$ sudo -u postgres psql</code>

Then:

<code class="">\password postgres</code>

Then to quit

psql

<code class="">\q</code>

If that does not work, reconfigure authentication.

Edit

/etc/postgresql/9.1/main/pg_hba.conf

(the path will differ) and change:

<code class="">    local   all             all                                     peer</code>

to:

<code class="">    local   all             all                                     md5</code>

Then restart the server:

<code class="">$ sudo service postgresql restart</code>

7. How to save PL/pgSQL output from PostgreSQL to a CSV file?

Answer:

Server-side

If you want something easy to re-use or automate, you can use Postgresql’s built-in COPY command. e.g.

<code class="">Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;</code>

This approach runs entirely on the remote server – it can’t write to your local PC. It also needs to be run as a Postgres “superuser” (normally called “root”) because Postgres can’t stop it doing nasty things with that machine’s local filesystem.

Client-side

The other approach is to do the file handling on the client-side, i.e. in your application or script. The Postgres server doesn’t need to know what file you’re copying to, it just spits out the data and the client puts it somewhere.

The underlying syntax for this is the

COPY TO STDOUT

command and graphical tools like pgAdmin will wrap it for you in a nice dialog.

The

psql

command-line client has a special “meta-command” called

\copy

, which takes all the same options as the “real”

COPY

, but is run inside the client:

<code class="">\copy (Select * From foo) To '/tmp/test.csv' With CSV</code>

Note that there is no terminating

, because meta-commands are terminated by newline, unlike SQL commands.

Your application programming language may also have support for pushing or fetching the data, but you cannot generally use

COPY FROM STDIN/TO STDOUT

within a standard SQL statement, because there is no way of connecting the input/output stream. PHP’s PostgreSQL handler (not PDO) includes very basic

pg_copy_from

and

pg_copy_to

functions which copy to/from a PHP array, which may not be efficient for large data sets.

There are several solutions:

psql

command

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

This has the big advantage that you can using it via SSH, like

ssh postgres@host command

– enabling you to get.

b. postgres

copy

command

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

c. psql interactive (or not)

<code class="">&gt;psql dbname
psql&gt;\f ','
psql&gt;\a
psql&gt;\o '/tmp/output.csv'
psql&gt;SELECT * from users;
psql&gt;\q</code>

All of them can be used in scripts, but you can prefer #1.

d. pgadmin but that’s not scriptable.

8. How to create a copy of a database in PostgreSQL?

Answer:

Postgres allows the use of any existing database on the server as a template when creating a new database. You should be able to execute the
following in a query window if it doesn’t:

<code class="">CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;</code>

Still, you may get:

<code class="">ERROR:  source database "originaldb" is being accessed by other users</code>

To disconnect all other users from the database, you can use this query:

<code class="">SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid &lt;&gt; pg_backend_pid();</code>

Alternative Solution:

To clone an existing database with Postgres you can do that

<code class="">/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid &lt;&gt; pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;</code>

It will kill all the connection to the source DB avoiding the error

<code class="">ERROR:  source database "SOURCE_DB" is being accessed by other users</code>

9. How to fix error “Fatal: role “username” does not exist”?

Answer:

Use the operating system user

postgres

to create your database – as long as you haven’t set up a database role with the necessary privileges that corresponds to your operating system user of the same name (

h9uest

in your case):

<code class="">sudo -u postgres -i</code>

As recommended here or here.

Then try again. Type

exit

when done with operating as system user

postgres

execute the single command

createuser

postgres

with

sudo'

The point is to use the operating system user matching the database role of the same name to be granted access via

ident

authentication.

postgres

is the default operating system user to have initialized the database cluster.

The manual:

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running

initdb

) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named

postgres

. In order to create more roles you first have to connect as this initial role.

Read about database roles and client authentication in the manual.

Also you can try this solution

In short, running

<code class="">sudo -u postgres createuser owning_user</code>

creates a role with name owning_user (in this case, h9uest). After that you can run

rake db:create

from the terminal under whatever account name you set up without having to enter into the Postgres environment.

10. How to fix error “Peer authentication failed for user “Postgres”?

Answer:

The problem is still your

pg_hba.conf

file (

/etc/postgresql/9.1/main/pg_hba.conf*

This line:

<code class="">local   all             postgres                                peer</code>

Should be:

<code class="">local   all             postgres                                md5</code>

If you can’t find this file, running locate

pg_hba.conf

should show you where the file is.

After altering this file, don’t forget to restart your PostgreSQL server. If you’re on Linux, that would be

sudo service postgresql restart

These are brief descriptions of both options according to the official PostgreSQL docs on authentication methods.

Peer authentication

The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password “sniffing” attacks then md5 is preferred. A plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the
connection is protected by SSL encryption then the password can be used
safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

Sample location for

 pg_hba.conf

/etc/postgresql/9.1/main/pg_hba.conf

11. How to drop a PostgreSQL database if there are active connections to it?

Answer:

This will drop existing connections except for yours:

Query

pg_stat_activity

and get the pid values you want to kill, then issue

SELECT pg_terminate_backend(pid int)

to them.

PostgreSQL 9.2 and above:

<code class="">SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid &lt;&gt; pg_backend_pid();</code>

PostgreSQL 9.1 and below:

<code class="">SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND procpid &lt;&gt; pg_backend_pid();</code>

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one you’re trying to drop.

Note the renaming of the

procpid

column to

pid

. See this mailing list thread.

12. How to Insert or perform multiple updates in PostgreSQL?

Answer:

PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL)

<code class="">INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;</code>

Searching PostgreSQL’s email group archives for “upsert” leads to finding an example of doing what you possibly want to do, in the manual:

Example: Exceptions with UPDATE/INSERT

This example uses exception handling to perform either UPDATE or INSERT, as appropriate:

<code class="">CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');</code>

There’s possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the hackers mailing list:

<code class="">WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;</code>

In Conclusion

This is the list for most frequently asked questions with solutions
about PostgreSQL. I hope this blog helped you with your problem. If you
haven’t found what you are looking for, please feel free to comment if
you need any help. We will get in touch with you as soon as possible.

This article was also posted on DevPostbyTruemark.