12 Most Asked PostgreSQL Questions

POUDEL SUNIL — Fri, 03 Jul 2020 07:26:57 +0000

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):

\d+ tablename

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:

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of 
table>';

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:

\connect DBNAME

or in short:

\c DBNAME

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

sudo -u postgres psql -c "CREATE SCHEMA test AUTHORIZATION test;" test

3. Which version of PostgreSQL are we running?

Answer:

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

SELECT version();

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

Server version:

pg_config --version

Client version:

psql --version

You can also use this method

Using CLI:

Server version:

$ postgres -V  # Or --version.  Use "locate bin/postgres" if not found.
postgres (PostgreSQL) 9.6.1
$ postgres -V | awk '{print $NF}'  # Last column is version.
9.6.1
$ postgres -V | egrep -o '[0-9]{1,}\.[0-9]{1,}'  # Major.Minor version
9.6

If having more than one installation of PostgreSQL, or if getting the “postgres: command not found” error:

$ locate bin/postgres | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/pgsql-9.3/bin/postgres -V 
postgres (PostgreSQL) 9.3.5
/usr/pgsql-9.6/bin/postgres -V 
postgres (PostgreSQL) 9.6.1

If locate doesn’t help, try find:

$ sudo find / -wholename '*/bin/postgres' 2>&- | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/pgsql-9.6/bin/postgres -V 
postgres (PostgreSQL) 9.6.1

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.

$ psql -V  # Or --version
psql (PostgreSQL) 9.6.1

If having more than one installation of PostgreSQL:

$ locate bin/psql | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/bin/psql -V 
psql (PostgreSQL) 9.3.5
/usr/pgsql-9.2/bin/psql -V 
psql (PostgreSQL) 9.2.9
/usr/pgsql-9.3/bin/psql -V 
psql (PostgreSQL) 9.3.5

Using SQL:

Server version:

=> SELECT version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

=> SHOW server_version;
 server_version 
----------------
 9.2.9

=> SHOW server_version_num;
 server_version_num 
--------------------
 90209

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.

=> \! psql -V
psql (PostgreSQL) 9.2.9

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)

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

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

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

Another way:

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

select 'drop table "' || tablename || '" cascade;' from pg_tables;

Or:

select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;

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:

select 'drop table if exists "' || tablename || '" cascade;' 
  from pg_tables
 where schemaname = 'public'; -- or any other schema

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:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

Check the listen_addresses and port in postgresql.conf:

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

#listen_addresses = 'localhost'     # what IP address(es) to listen on;
#port = 5432                # (change requires restart)

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:

brew && brew list|grep postgres
fink && fink list|grep postgres
port && port installed|grep postgres

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

brew services start postgresql

and

brew services stop postgresql

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

brew services start postgresql@10

6. How to change the PostgreSQL user password?

Answer:

For passwordless login:

sudo -u user_name psql db_name

To reset the password if you have forgotten:

ALTER USER user_name WITH PASSWORD 'new_password';

Option2

Type:

$ sudo -u postgres psql

Then:

\password postgres

Then to quit psql:

\q

If that does not work, reconfigure authentication.

Edit /etc/postgresql/9.1/main/pg_hba.conf (the path will differ) and change:

    local   all             all                                peer

to:

    local   all             all                                     md5

Then restart the server:

$ sudo service postgresql restart

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.

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

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:

\copy (Select * From foo) To '/tmp/test.csv' With CSV

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:

a. 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)

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

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?

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:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Still, you may get:

ERROR:  source database "originaldb" is being accessed by other users

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

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

Alternative Solution:

To clone an existing database with Postgres you can do that

/* 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 <> pg_backend_pid();

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

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

ERROR:  source database "SOURCE_DB" is being accessed by other users

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

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):

sudo -u postgres -i

As recommended here or here.

Then try again. Type exit when done with operating as system user postgres.

execute the single command createuser as 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 in the manual.

Also you can try this solution

In short, running

sudo -u postgres createuser owning_user

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:

local   all             postgres                                peer

Should be:

local   all             postgres                                md5

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:

Querypg_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:

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 <> pg_backend_pid();

PostgreSQL 9.1 and below:

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 <> pg_backend_pid();

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)

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;

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:

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');

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

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;

In Conclusion

This is the list for most frequently asked questions with solutions about PostgreSQL. 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.

DEV Community: POUDEL SUNIL

12 Most Asked PostgreSQL Questions

Rundown For The 12 Most Asked PostgreSQL Questions

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

2. How to switch databases in PostgreSQL?

3. Which version of PostgreSQL are we running?

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

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

6. How to change the PostgreSQL user password?

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

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

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

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

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

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