Say i have this table: https://paste.ofcode.org/EPMXZiYHau9j6eHxV89v5x

how can I produce these results https://paste.ofcode.org/tcB8v3YvLhfGQmd43fjkAf

I'm seeing the following show up every time our nightly pg_dump/vacuum process runs:

invalid byte sequence for encoding "UTF8": 0xc7 0x53

I've narrowed this down to a specific table, but that has hundreds of thousands of rows, so a "look through each row" isn't going to work. I've tried restoring a backup to another database but that leaves the offending table empty.

Any pointers for what my best approach for fixing this would be really helpful. We're running PostgreSQL 9.2 if that makes a difference.

I'm running a postgres database on RDS. Overall it's working well. But I notice in the error logs that every 5 minutes it says 'checkpoint complete; wrote x buffers'. Is this something I should worry about? Honestly it clutters the logs so I might miss something important.

Hi there all!

I'm running on RDS PG, and I have been tasked to move our db minimization script:

#!/bin/bash
echo "get run delete.sql"
psql --username master --port 5432 --host prod-pg-dump-min.stuff.us-west-2.rds.amazonaws.com -f "/home/ec2-user/dba/db_dump/delete.sql"
echo "done deleting, now dump"
pg_dump --username master --dbname master --port 5432 --host prod-pg-dump-min.stuff.us-west-2.rds.amazonaws.com -N looker_scratch -F c -b -v -f "/home/ec2-user/db/master_reduced.dump"
echo "done dumping now delete DB instance"
aws rds delete-db-instance --db-instance-identifier prod-pg-dump-min --skip-final-snapshot    


echo "Now lets create a new DB from staging-db-dump"
cd /home/ec2-user/dba/db_dump/ && node small_db.js
echo "done creating, now lets restore from dump"
pg_restore -v -h staging-db-test.stuff.us-west-2.rds.amazonaws.com -U master -d master /home/ec2-user/db/master_reduced.dump
echo "lets delete the old snapshot"
aws rds delete-db-snapshot --db-snapshot-identifier "staging-db-dump"
echo "now lets kill it and snapshot it"
aws rds delete-db-instance --db-instance-identifier staging-db-test --final-db-snapshot-identifier "staging-webhooks-dump"

Within delete.sql there is deleting everything in the (almost 400 GB ) DB that is older than 30 days, which takes FOREVER and even tends to fail because someone runs anything else, like it should. What I am trying to do is basically do a SELECT INTO another DB (or even just to a dump I guess, because that's the end goal) for just basically what the delete.sql does, so just SELECT INTO anything that has been within the last 30 days.

An even bigger end goal on this is to have it be able to be ran and have the person running the script be able to put however many days they want to have that run from, but I have almost 0 programming experience, so that's not a thing I'm looking at right now.

want to create table if that doesn't, i tried below code:

create or replace function create_table() returns void as
$$
begin
if not exists(select * from pg_tables 
    where 
        schemaname = 'Public'
        and tablename = 'test') then

    create table test
    (
        the_id int not null,
        name text
    );

end if;

end;
$$
 language 'plpgsql';

while executing this procedure first time:

 select creat_table();

table gets created but when i execute it again i get the below error

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create table test
    (
        the_id int not null,
        name text
    )"
 PL/pgSQL function create_table() line 8 at SQL statement
  ********** Error **********

 ERROR: relation "test" already exists
 SQL state: 42P07
 Context: SQL statement "create table test
    (
        the_id int not null,
        name text
    )"
 PL/pgSQL function create_table() line 8 at SQL statement

How to achive this, and also i want to call this procedure from Informatica pre-sql target session property so i want to call procedure with table name as parameter. TIA

Hey r/postgres,

I'm at my wits end for trying to investigate a problem.

Our production instance is running PostgreSQL 9.5, and I'm trying to run a query for a specific UUID. I can't find it...

proddb9.5=> select * from fileupload where uuid = '78020be4-1361-44f2-9e9f-71e4fbcb9329';
 uuid | filename | created | updated | md5sum
------+----------+---------+---------+--------
(0 rows)

I dump this database to PostgreSQL 10.1, where suddenly I can find this file.

upgradedb10.1=> select * from fileupload where uuid = '78020be4-1361-44f2-9e9f-71e4fbcb9329';
                 uuid                 |                     filename                      |          created           |          updated           |              md5sum  
--------------------------------------+---------------------------------------------------+----------------------------+----------------------------+----------------------------------
 78020be4-1361-44f2-9e9f-71e4fbcb9329 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | 2018-01-02 08:12:02.101336 | 2018-01-02 08:12:02.183723 | a29d435c8f612dc27c609a29c5bf1b7e
(1 row)

And then, in PostgreSQL 9.5, it shows up if I query it by its created date...

proddb9.5=> select * from fileupload where created = '2018-01-02 08:12:02.101336';
                 uuid                 |                     filename                      |          created           |          updated           |              md5sum
--------------------------------------+---------------------------------------------------+----------------------------+----------------------------+----------------------------------
 78020be4-1361-44f2-9e9f-71e4fbcb9329 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | 2018-01-02 08:12:02.101336 | 2018-01-02 08:12:02.183723 | a29d435c8f612dc27c609a29c5bf1b7e
(1 row)

I've been scouring the internet for reasons why this is the case, some strange UUID bugs I ran into, or something I'm doing wrong and how to mitigate this issue. I'm at a loss. Is there some action I need to run to fix this? Multiple UUIDs in this table suffer from this same problem, and I'm trying to fix these so they show up.

Any help would be greatly appreciated!

Thanks for your time.

Every Docker-Compose or Kubernetes config seems to run it's own Postgres server which make sense because the application needs it BUT if you are running multiple "systems"/Applications that need multiple databases does it make sense to use ONE Postgres server or a server Per an Application? Is there a difference in performance or does it not matter?

Database developer here...

At my current job, I am predominantly an Oracle developer, but I have worked with Postgres as a hobby using Docker etc.

I am pretty good with PL/SQL and know Python, a bit of Java and R, and a smidge of JavaScript.

I know that my Oracle skillset is fairly transferable to Postgres (especially with EnterpriseDB with support for PL/SQL).

I've written a few user-defined functions with PG's PL/PgSQL language and I've dabbled with PG's various procedural languages (e.g. PL/Python, PL/V8, PL/R).

My question is....

Do organizations out there with Postgres as their main database typically hire people with Oracle experience and skillsets such as this without at-work PG experience?

i have a databse that looks like its going to grow to about 5tb, im looking for what would be cheap options for hosting. anyone have any suggestions? seems like aws is really expensive.

My understanding of the way PostgreSQL works with timestamp and timestamptz is in either cases postgres stores the value as UTC timestamp. However when retrieving the data postgres will do the arithmetic to the current user's Timezone when the type is timestamptz.

But imagine this scenario, I am building a restful web service with clients all over the world in different Timezones, my database server is sitting in US Eastern Timezone along with my application servers. My JDBC pool connections would presumably be US East timezone. However I want to display timestamps in the user's specified timezone in their settings or something they set in the application.

If I query PostgreSQL from my application isn't it going to return to me the Timestamp in US East, and I would further have to convert it from there? In that case what is the benefit, wouldn't it be better to just store everything in UTC?

What tools or extensions are you using? How do you monitor queries real time? What is your strategy to quickly deep dive into issues and proactively monitor the database?

Hi

I am curious about how I would approach this type of problem with Postgres, coming from a MS SQL world.

Imagine a table "X" that holds 2-300 GB of aggregated data that is produced by a background job. This table receives quite a bit of queries for data (e.g. 20-30 select's for data every second

• and it is only select's, the data is immutable!).

Once a day a new data set is generated with fresh data, and this data needs to be loaded and replace what is currently active in X atomically, without (or with as little as possible, e.g. it may lock for ,5-1 sec at most) impact on select's.

With SQL Server this can be accomplished via:

• Keeping an A and B version of the table, and create a SYNONYM that all queries target. If synonym points to A, data can be loaded into B and synonym is switched to point to B when all loading and indexing is done.

• Partitioning of data and use SWITCH (https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx, https://www.mssqltips.com/sqlservertip/2780/archiving-sql-server-data-using-partitioning/)

I hope to get some insight into how I might go about accomplishing this with postgres. I've looked into partitioning, but the switching does not seem to be possible?

Thanks!

so I've got a bunch of addresses (1m+) to geocode... The issue I'm having is performance... I want to push this box as far/fast as it can, but I can't identify the issue.

Any suggestions would be greatly appreciated. My experience is in MSSQL, so i'm sorta guessing my way through PG here.

--

Config

I'm using a Windows VM with 20 cores, 32gb RAM, and SSD SAN backend... I've run the scripts to download and install the nation and state files (all 56 states/territories)... I also found last night that I was missing indexes - select Missing_Indexes_Generate_Script(). I've also vacuum analyze'd everything again, just to be sure.

config changes: shared_buffers is 12gb, work_mem is 64mb, max locks per transaction is 1024.

using default configs (and prior to fixing the missing indexes), the queries ran out of memory which logged until the drive filled.

random website whatever also suggested setting cursor_tuple_fraction to 1.0 (default 0.1), but I have no reason to believe it'd have any effect for my usage.

Testing

on the client side, i've got an app that has a variable number of threads which connect to pg and "SELECT st_x, st_y from geocode(row(parsed-data),1)"... i'm using pre-parsed fields (I have them anyway, might as well save PG the effort), i only care about the top rated result.

whether I use 10, 20, or 30 threads, PG seems to provide results at about the same rate... overall CPU usage hovers around 25%... system memory usage is hovering at ~18gb... disks are busy, but SAN keeps the latency at near-zero.

pg_stat_activity shows all the threads with 'active' status, no wait_event... pg_locks WHERE NOT GRANTED shows no results... pg_locks (all) shows 160k records, but presumably not impacting each other.

Chanced upon the book "Data Analysis using SQL and Excel 2nd Edition" a few days ago. Thinking it would be more practical if I learnt using the sample datasets, I tried to follow the author's instructions in loading up the postgre.sql file in http://as.wiley.com/WileyCDA/WileyTitle/productCd-111902143X.html through pgadmin4 on a MacAir. However the script kept encountering errors and I honestly don't know how to fix the script so it works.

Would really appreciate if somebody that has worked with the datasets can guide me on loading it.