PostgreSQL

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

Share tech news, updates, or what's on your mind.

Sign up to Post

Can anyone share a sample full contents of recommended / hardened settings
of postgresql.conf ?

What's indicated in CIS benchmark for Postgresql 10 is unclear & in bits & pieces;
some of the extracts from the benchmarks are posted below:

- configuration file enumerates all tunable parameters and even though most of them are
commented out it is understood that they are in fact active and at those very same
documented values.

- shared_preload_libraries = 'pgaudit'
OR
shared_preload_libraries = 'pgaudit,somethingelse'

- $ vi ~postgres/10/data/postgresql.conf
# load set_user libs before anything else
shared_preload_libraries = 'set_user, other_libs'
0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Team,
I'm curious what the above title is as far as a command to run via postgres.exe

Any help here would be great!
Thank you!
Aaron.
0
Hi,

How to suppress milliseconds in timestamp without time zone or Timestamp  data type field in Postgresdatabase/Greenplum.
for instance if you have a Function which inserts data into a table  of field of data type timestamp , we wanted to make sure we only enter yyyy-mm-dd hh:mi:ss , No milliseconds  across the board.   Could  some one please advise.
0
Postgres database corrupted

We have a cluster postgres corrupted working with drbd  ,  we have repaired  xfs

But vacuum  is failing
 sd_produccion=# vacuum FULL ANALYZE VERBOSE xxxx.transaccion ;
INFO:  haciendo vacuum a «xxxx.transaccion»
ERROR:  la página no es válida en el bloque 1416180 de la relación base/16429/118805272

We can not access to some tables error with reindex
0
How to set  default  trim for trailing spaces on strings/characters data in PostgresSQL

if you have string 'abc   '     it should 'abc'.   don't want to apply any trim functions manually on each query, Please advise.
0
Capturing events for SQL Server 2017 - Linked Server to PostgreSQL..

I have tried using events such as:

CREATE EVENT SESSION [RC3cx3] ON SERVER
ADD EVENT sqlserver.oledb_data_read(
    ACTION(sqlserver.database_name,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'L:\MSSQL\RC3cx_new.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

But I cannot get any data to be logged.

Scenario is a web based app inserting, deleting and updating data and I want to see what tables are being hit, in what order and data required to develop a side app.

Please advise on best way forward.

Many thanks in advance

R
0
how to move data from sonarqube (7.4) on postgres standalone server, to other ubuntu server on docker sonar with postgres?
What i did:
i install sonar with postgres on docker sonar 7.7-community and postgres 11.3
then i take dump of old sonar and restore on new postgres.
it's not work.
0
Hi,
I have the following problem to be solved.
There are two tables
1) applications
2) docs
each application has a set of docs.

mark all applications with status ='skipped' to status = null and in the corresponding docs associated with this application mark a column attr to null.

I initially did it like this -
1) fetch all the applications with status = skipped
for each application

update application set status = null
   update docs set attr = null

looping over each application id. so for example if there were 100 applications i will be making 200 queries
normally i am expecting 300-400 applications but it could go up to 10K

I am not sure but on the surface this does seem like a bad strategy if i had to make 10K* 2 db calls


2)
Since we have list of all application ids
update docs set attr= null where application_id in ();
   update applications set status = null where id in ();

So in this case no matter how many applications are there it will need only 2 queries
Also the initial one query to fetch all the applications so a total of 3 queries exactly no matter how many applications are there.

Here i am not sure about the performance implications of in clause. but it does seem faster on the surface due to minimal number of queries.

Please comment on this approach if this will be better.

Also is there any way to achieve the same in less than 3 queries or a faster way ?

Thanks
0
I try to run sonarqube 7.7. on docker with postgresql database.
i running artifactory on postgresql and i using default port 5432.
When i try sonarqube and in my docker compose change port to 5430 web server want to start.
how to use same docker postgresql for both artifactory and sonar.
i mean how from compose-file tell sonar to use existing container (of course i login to postgres and create a database for sonar)
0
need a simple and direct method to rename the admin account for PostGre-SQL 9.3. I am using the pgadmin application but cant get the password changed. I wonder if there is an alternate way for me to get this process done. maybe the SQL Shell or something else in the actual PGAdmin suite. recently lost access to the admin account for this Postgre Sql. I have been reading around on ways to get it done but none of them seemed to work out for me yet.
0
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Hi,

we are getting syntax error when we have a return table column matches with any other table columns in the function. If we need to use of one of return table column data and insert into another table which has same column name, it seems, it is not allowed, any work around on this? please find below code.


CREATE OR REPLACE FUNCTION public.get_country9() 
 RETURNS TABLE (
  [b]country_id[/b] integer,
  country character varying(50),
  last_update timestamp without time zone
  )
AS $BODY$

BEGIN
 RETURN QUERY 
 SELECT
 c.country_id,
 c.country,
 c.last_update
 FROM
 public.country c;
  
insert into public.test_insert
(  city,
  [b]country_id[/b]) 
 select 'LONDON',222;

 insert into public.test_insert
(  city,
  [b]country_id[/b]) 
 select 'TORONTO',111;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

Open in new window



ERROR:

ERROR: 42601: syntax error at or near "$1"

Open in new window

0
is there any way to create master slave for postgresql when slave is on docker installation ?
0
Hi,

  We are on Postgresql 9.7
There is a table "Table-1" with the following fields.
ID                Name                DOB               SSN                   Start_dt            End_dt
1                   Kevin               2/3/2001      xxx-xx-2354        1/1/2018         12/31/9999

Open in new window

Whenever there is an update, let's say, to the DOB, the current row gets end dated and a new row gets inserted, like below:
ID                Name                DOB               SSN                   Start_dt            End_dt
1                   Kevin               2/3/2001      xxx-xx-2354        1/1/2018         3/17/2019
2                   Kevin               2/3/2000      xxx-xx-2354        3/17/2019         12/31/9999

Open in new window


My question is related to a trigger to capture the old and new values of the DOB , which is a mix of UPDATE and INSERT.
First off, how can we sense if it is a change to the existing entry or a new insert, in this CDC Type-2 scenario?

Please help
Thank you!
0
I need someone who understands shell scripting in a PostgresSQL environment to look at this.
I am trying to get the Pgbackrest  (ver. 2.10) application to provide notifications by Email when it encounters errors.
The error reports (either from a logfile or the command pgbackrest info) should be included in the notification.
The email are to be sent from a RHEL server.
CRON tasks currently used sends empty notification emails even when there are no errors.
0
Steps to install PostgreSQL 6.9 on a Oracle Virtual Box on WIndows 10 computer.
0
Does anyone know of a good tool to handle postgresql differential BU without rep, currently I am using native Wal procedure, but I am investigating, there are some good Linux tools, but could not find any windows apps, that don't blow my developer one person budget
0
I am running Wal backup, I am looking for the correct string for pg configuration files, that will copy my last successful Wal backup files to another local directory,  

bu wal is running fine  minutely, I now need to have the finalized files pushed for pg to another dir, if I monitor the wal dir, and move some files, that are still active somehow, pg will crash
0
Hi,

   In DB2, we can declare a temporary table in a Stored Procedure SP1 and use it in another stored procedure SP2, while calling SP1.

Is there a way to do it in Postgresql 9.7?
Please help with an example, if there's a way to it?
Thanks a lot!
0
Hi,

   We are on Postgresql 9.7
I have two tables.
Table-1 has about 100 codes. As an example,
ID        Code
1           'M'
2           'C'
3           'R'

My table-2 has a code  field, which needs to only allow a subset of the codes from Table-1.
Example: Table-2 can only have values 'M' and 'R'.
Is it possible to create a constraint or something?
  Please advice.
0
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Create a continuous test for PostgreSQL from the server to the client computer. We have an ERP using PostgreSQL database and we have random disconnects from the database/server. I know it is either network or server related but need to figure out how I could create a continuous test of some sort.

Any idea how I could troubleshoot this?
0
Hi,

 We are on Postgresql 9.6
I have a table with fields like this:

   ID                 Status                    Start_Dt                     End_Dt
    1                    AP                        01/01/2018                01/31/2018
    2                    AP                        02/01/2018                02/28/2018
    3                    AP                        03/01/2018                03/31/2018
    4                    AP                        04/01/2018                 04/30/2018
    5                    DE                        05/01/2018                05/31/2018
    6                    AP                         06/01/2018               06/30/2018
     7                   AP                         07/01/2018                07/31/2018
    8                    CS                          08/01/2018               08/31/2018

I need the result like the following:

     Status                        Start_Dt                     End_Dt
        AP                         01/01/2018            04/30/2018
        DE                         05/01/2018            05/31/2018
        AP                          06/01/2018            07/31/2018
        CS                          08/01/2018            08/31/2018

Basically, need to stitch up the dates based on the change in  the Status.

Can someone please help with the solution?
0
PostgreSQL world :

With pgbadger (postgres tool), some results speak about SESSIONS, others about CONNECTIONS.

With PostgreSQL, what is the difference between a session and a connection ?

Your site contains the answer for Oracle but not for Postgres...

Thanks a lot.

Gery
0
Hi,

Can you please help me to write SQL script in PostgreSQL for source data profiling. I can't use any data profiling tools, so I need to write SQL scripts. These are some of the things I want the result:

TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ColumnDataLength
DataType
MinDataLength
MaxDataLength
AvgDataLength
MinDate
MaxDate
NoDistinct
NoNulls
NoZeroLength
PercentageNulls
PercentageZeroLength
NoDateWithHourminuteSecond
NoDateWithSecond
NoIsNumeric
NoIsDate
NoAtLimit
IsFK
DataTypeComments

I have attached screenshots which is similar to what I am looking for. It is not important to have all results in one script. It can be different scripts but I want column_names in result set. Thank you.17375.jpg17377.jpg
0
I have ubuntu 16.04 server with postgres version 9.3.17 . how to update 9.3.17 to 9.3.24?
i know it's old but for master/slave version i need to have a same version as master.
0
I am trying to retrieve the average value from a PostgreSQL database using a Sequelize ORM with React on the front end and running Node.js server.  My 'GET' request on the Node side retrieves all of the data from the PostgreSQL database so I know everything is wired up with React using the following code:

module.exports = function (db, app, bodyParser) {


    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));

    app.get('/survey_init', function(req, res, next) {
        db.answers.findAll({})
        .then(function (data) {

            res.send(data);
              })
          .catch(function (err) {
            return next(err);
          })
        });
        
    }

Open in new window


However, when I attempt to run an average on a specific column called 'conf_one' in my PostgreSQL database I get an error.  I will list the modified code (modified from code above) that is throwing the error here:

module.exports = function (db, app, bodyParser) {


    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));

    app.get('/survey_init', function(req, res, next) {
        db.answers.findAll({
          attributes: ['conf_one', [models.Sequelize.fn('AVG', models.Sequelize.col('conf_one')), 'conf_one_avg']]
        })
        .then(function (data) {

            res.send(data);
              })
          .catch(function (err) {
            return next(err);
          })
        });
        
    }

Open in new window



The error that I get with the code is the following error and I do not get the result of an average calculation.


Open in new window

0

PostgreSQL

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

Top Experts In
PostgreSQL
<
Monthly
>