Skip to main content
46 votes

Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?

Here's the trick, at least with pgAdmin v4: Leave the tablespace blank. It will default to "pg_default" when creating the database.
Logan's user avatar
  • 561
43 votes

CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6

ANALYZE VERBOSE; You need to run an ANALYZE query on the whole database using the above command. The issue is that the query plans postgres has generated are optimised for the previous version of ...
tannart's user avatar
  • 531
22 votes

Best way to pg_dump PostgreSQL on RDS to S3

You can directly upload the (gzipped) pg_dump results to s3 using the aws cli: pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --...
Birne94's user avatar
  • 371
22 votes
Accepted

What does KEY SHARE OF in a PostgreSQL statement mean?

This is the effect of inserting or updating a row in a table that has a foreign key. See the following example: CREATE TABLE a (id integer PRIMARY KEY); CREATE TABLE b (id integer REFERENCES a); ...
Laurenz Albe's user avatar
  • 53.4k
16 votes
Accepted

Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

Here is a query what will generate all the necessary update statements. It temporarily disables foreign key checks so that the update statements succeed. It updates the default character set for each ...
Stephen Ostermiller's user avatar
16 votes

MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online

You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping ...
AMtwo's user avatar
  • 16.2k
14 votes

Does amazon rds PostgreSQL require vacuum?

The RDS Documentation states: Autovacuum is enabled by default for all new Amazon RDS PostgreSQL DB instances, and the related autovacuum configuration parameters are appropriately set by default. ...
HorsePunchKid's user avatar
14 votes

How to grant set the replication attribute on RDS PostgreSQL?

We hit this head on too, and after much frustration discovered: CREATE ROLE some-user WITH LOGIN PASSWORD 'long-password'; grant rds_replication to user; This seems to be working for logical ...
mlissner's user avatar
  • 583
14 votes
Accepted

Adding a Primary Key to a Large PostgreSQL Table with High Traffic

Why? Your step 1 would need a lot more than 600 GB (temporarily). The table has around 2 TB. About as much (minus possible bloat, plus 8 bytes per row for the new bigint column) has to be available at ...
Erwin Brandstetter's user avatar
13 votes

Does AWS RDS support MEMORY storage engine?

This answer applies to AWS Aurora not straight MySQL RDS. Just because SHOW ENGINES says the database has the MEMORY storage engine doesn't mean you can actually use it. We have found that in AWS ...
Barry Coleman's user avatar
12 votes

How to alter system on AWS RDS?

The RDS Postgres instance has a parameter group. Check in RDS colsole what parameter group is defined: RDS -> Databases -> choose your db -> Configuration -> parameter group. If it's the default one, ...
detzu's user avatar
  • 396
11 votes

ERROR: no schema has been selected to create in

I've tried multiple solutions, and keep getting the same error over and over. Worse to mention, the error appears to me when I needed to restart migration of my DB by deleting public schema and after ...
Kostanos's user avatar
  • 211
11 votes
Accepted

Improve UPDATE performance on big table

Assuming the column uuid is supposed to be UNIQUE, this table definition should save some space and help performance: CREATE TABLE public.calls ( id serial PRIMARY KEY, result int4 DEFAULT 0 ...
Erwin Brandstetter's user avatar
10 votes

ERROR: no schema has been selected to create in

I had pgdump file with creating functions in custom schema and I wanted to switch custom schema name to general public and replaced all occurences with old schema to empty (e.g. myschema.tablename to ...
Panoptik's user avatar
  • 201
10 votes
Accepted

AWS DMS creates tables with no auto_increment

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like ...
Elliot Chance's user avatar
10 votes
Accepted

MySQL commits taking time

Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to ...
Aurimas Mikalauskas's user avatar
9 votes

How do I configure AWS Aurora to separate write/read operations

I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google. https://aws.amazon.com/blogs/aws/new-reader-...
Tony's user avatar
  • 99
9 votes
Accepted

Achieving "sequence number", rather than dateTime, for updates; postgres specifically

As a_horse_with_no_name originally suggested in question comments: --assuming we have tables: a, b, ... CREATE TABLE a ( a_id integer NOT NULL PRIMARY KEY, a_name text ) ; CREATE TABLE b ( ...
ypercubeᵀᴹ's user avatar
9 votes

Why am I getting permission denied on an ownership change?

Thanks to @Spike for comment, for solution which worked for me (source) This assumes that objects are reassigned from role admin to role new_admin. 1.Create a new role: mydb=> CREATE ROLE ...
Oto Shavadze's user avatar
9 votes
Accepted

How to add index to a big table with 60M records without downtime?

For this example, let say you have the following in the database mydb CREATE TABLE mytable ( id INT NOT NULL AUTO_INCREMENT, num INT DEFAULT 0, dat VARCHAR(32), PRIMARY KEY (id) ); ...
RolandoMySQLDBA's user avatar
8 votes

Diagnosing high write throughput on RDS Postgres

It turns out that the vast majority of my write throughput was coming from the stats collector. Because of the very high number of relations in my database, the stats data is unusually large. I was ...
josh's user avatar
  • 271
8 votes
Accepted

pg_dump permission denied for sequence

Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped. pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name
ZeroMax's user avatar
  • 196
8 votes
Accepted

Postgres 9.4 wal_level=logical increases disk space

This is an AWS DMS issue. DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL ...
Aravindan R's user avatar
8 votes

Question regarding PSQLException: User was holding shared buffer pin for too long

If the replay process is already behind by more than max_standby_streaming_delay (or max_standby_archive_delay), then it will immediately terminate anything which is in its way. If it is behind by ...
jjanes's user avatar
  • 40.1k
7 votes
Accepted

"Cannot resolve table name" error when applying MySQL dump to RDS

Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11. Extract from the changelog: InnoDB: Creating a table with a full-text index and a foreign key constraint ...
BenMorel's user avatar
  • 712
7 votes
Accepted

Amazon RDS remove read-only and allow defining table structures

In my case, I ran into this problem on one of my stacks—but not the first time the stack was built. It seems I was referencing an AWS::RDS::DBInstance's DBInstance.Endpoint.Address, but that instance ...
geerlingguy's user avatar
7 votes

Is there any way to replace sql server file_guid with new GUID?

We had to do the same - as we wanted to copy a database to a new database, changing the name and file IDs at the same time. We found the original process posted here to not to be repeatable, I guess ...
NJITman's user avatar
  • 71
7 votes
Accepted

Does AWS RDS support MEMORY storage engine?

Yes, it does. How come ? Run this query in the RDS Instance: mysql> select * from information_schema.engines where engine='MEMORY'\G *************************** 1. row *************************** ...
RolandoMySQLDBA's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible