People who code: we want your input. Take the Survey

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
2
votes
1answer
237 views

postgresql - transaction log - versioning?

Is it possible to "version" your database so that any change can be reviewed or roll backed? Obviously backups are important but being able to go back to any previous state of the database (at least ...
6
votes
2answers
5k views

Ways to speed up IN queries under PostgreSQL

I have a query of the following form: SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000) The IN query has anywhere from 10 to 25000 values. The query runs for minutes ...
9
votes
2answers
9k views

How to specify trigger execution order under PostgreSQL?

I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table. CREATE TABLE twitter_interactions(...); CREATE OR REPLACE ...
9
votes
2answers
1k views

Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting

While writing a query other day a thought came to me and have stuck in my mind. What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...
11
votes
2answers
10k views

View Postgresql Memory Usage

I'm running Postgresql on a Ubuntu server and need to be able to monitor its memory usage. Currently I have scripts running in a one-minute cron job that monitor/log various statistics and need to ...
2
votes
1answer
3k views

Postgres Index scan forward vs backward = speed difference of 357X slower

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit Dedicated DB server 4GB ram Shared_Buffers = 1 GB Effective_cache_size = 3GB Work_mem = ...
4
votes
1answer
59 views

Want to add a “variable” to a table

I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
1
vote
2answers
1k views

Tables accessed during last period

I want to check which tables have been updated during a given period, for instance in descending order of access time per table. How can I get that for PostgreSQL?
2
votes
1answer
3k views

Postgresql revert back database value

I have postgresql database. My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table. When I get the data file using following commands. select * from ...
0
votes
1answer
4k views

postgresql recovery after reboot

I installed Postegresql and added a database which I was using as part of a local server. However, I restarted the computer and my Postgresql database seems to be gone. The Postegresql documentation ...
3
votes
2answers
2k views

How does pgAdmin III build CREATE statements?

In PostgreSQL, there is no SHOW CREATE statement, known for example from MySQL. But when you use the pgAdmin III client program and go to object details, you see the corresponding CREATE statement. ...
4
votes
2answers
3k views

PostgreSQL unexplained table bloat

I have a table in a Postgres 8.2.15 database. The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB. A few weeks later ...
0
votes
2answers
999 views

Slony - Wait for Event

What is the behavior of Slony waiting for an event after lock set command? Basically I am trying to do a switchover and it takes a lot of time. We are following these steps: Lock Set Wait for Event (...
1
vote
1answer
6k views

restoring a table from sql file in postgresql through Windows Command prompt

Im trying to restore an sql file which contains the dumpf a table (with COPY commands) into a table into the database, it would be great if someone could share an example, as the pg_restore doesn't ...
167
votes
6answers
240k views

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I ...
0
votes
1answer
566 views

Which file is PostgreSQL log file 0?

In my PostgreSQL logs, I find this message: LOG: invalid magic number 0000 in log file 0, segment 1, offset 16121856 Which file is log file 0? Here is my pg_xlog directory: -bash-4.1$ ll pg_xlog/ ...
6
votes
2answers
7k views

Should arrays of IDs stored in Postgres use varchar, int, or another type?

What is proper convention for creating a field that will hold an array of foreign keys? In this particular case, I'm creating a field called "owners" that will hold an array of user IDs. I want to ...
10
votes
1answer
15k views

Postgresql: Move database to new tablespace

I'd like to completely move a database (tables, indexes, etc.) to a new tablespace. I know how to move each item individually but is there a way to move it all in one go? I'm using postgresql 9.1.
1
vote
2answers
8k views

Running postgresql in Mac Terminal

I have a production SQL dump file that I want to restore to my local psql db for debugging reasons. I can't access psql via the terminal though pgAdmin3 works just fine. Here is the error I'm getting:...
2
votes
1answer
295 views

Streaming Replication on Two Postgres Systems?

There's two servers, a Primary Server and a Backup Server. Both have Postgres installed. The Primary Server has one database, and the Backup Server has two - one for a warm backup/standby of the ...
4
votes
2answers
3k views

How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

I'm working on a project that must support two database engines; SQL Server and PostgreSQL. We are using NHibernate as the ORM. We are running into performance issues with certain queries. Using ...
2
votes
1answer
126 views

How to recover the database with old data?

I am using PostgreSQL. I have 4 database servers. Each server has same database and same schema. Mistakenly I restored the database util05db backup onto the util01db database. How can I get the ...
11
votes
2answers
11k views

PostgreSQL for high volume transactions and for Data warehousing

Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using ...
2
votes
1answer
8k views

Recover PostgreSQL database data

I am using PostgreSQL in my project, Now by mistake I have deleted some records of one of my tables. Now is there any way to recover deleted Records in PostgreSQL?
2
votes
2answers
3k views

Update column by comparing date and string values together

I have problem in updating table. I need to update table by comparing date and name with another table. I have two tables: table 1 : dim_sesid name(varchar) role ...
0
votes
1answer
6k views

PostgreSQL Query and/or Session CPU Usage

Is there any way in PostgreSQL to know how much the cpu cost was per a query and/or by session. It doesn't have to be the exact system cpu usage more of a relative cost like we get with disk I/O. For ...
3
votes
1answer
146 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
3
votes
1answer
2k views

create index - invalid memory alloc request size

i get this error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000" when i want to create an index. The Table looks like this: CREATE TABLE schema.tblname ( id bigint NOT ...
54
votes
4answers
27k views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
6
votes
2answers
4k views

PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID

For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and ...
14
votes
6answers
28k views

SQL Query Formatter [closed]

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux (...
19
votes
4answers
32k views

List primary keys for all tables - Postgresql

Is there a query that will do that? I found some queries that can do this for one table, but I wasn't able to modify it so I can see: tablename | column | type
0
votes
2answers
243 views

New PostgreSQL 9.1 configuration doesn’t work

I changed the length of table names in PostgreSQL in the file pg_config_manual.h (in C:\Program Files\PostgreSQL\9.1\include) to 128. Then I reloaded the new configuration by selecting menu "Reload ...
4
votes
2answers
328 views

Deadlock during Slony uninstall and concurrent access of DB

I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process. I was trying to uninstall node from a cluster, as per slony's documents this requires access ...
7
votes
2answers
3k views

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
1
vote
2answers
2k views

Backup of Postgres database

I want to create a backup of my database in Postgres. I'm using pgAdmin. on my database I have clicked right button of mouse after that I've selected the "Create a backup of database" After that I'...
50
votes
1answer
25k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
14
votes
1answer
5k views

PostgreSQL failover and replication

I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details. I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I ...
3
votes
1answer
2k views

Working across multiple PostgreSQL database servers

So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but ...
16
votes
3answers
11k views

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
120
votes
8answers
173k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
2
votes
1answer
810 views

Restoring a postgres8.3 backup with postgres8.4

I'm trying to restore a backup dumped from a postgres 8.3 server in a server with postgres 8.4 with this command: pg_restore --host host --port 5432 --username username --dbname my_db /route/to/...
4
votes
1answer
2k views

How can I escape XML field in XML-output query?

I have following request: SELECT XMLAGG(data) FROM (SELECT XMLELEMENT(name item, XMLFOREST( title, body as description, date_created as date, 'http://example.com/news'||id as link )) AS ...
4
votes
2answers
4k views

Copying the database from SQL Server to PostgreSQL

I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of ...
2
votes
1answer
600 views

Rewriting a query

I have a database with the following structure (star schema): facttable: pros integer mena integer me integer vahet double precision slutttp timestamp without time zone Structure of each ...
5
votes
1answer
7k views

Postgres PgAdmin III Query Builder crash

I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem. I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300. ...
10
votes
2answers
6k views

Can PostgreSQL create a case sensitive database?

I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to ...
13
votes
1answer
13k views

PostgreSQL Database Data File Integrity Check

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption? In the ...
17
votes
3answers
5k views

Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?

Context: Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the ...
2
votes
1answer
669 views

Why is a duplicate record inserted when it should not be?

Why does this command insert into "deliveryLog" select * from temp_dellog where "deliveryID" < 15620652 give this output? ********** Error ********** ERROR: duplicate key value violates unique ...

1
262 263
264
265 266
269