47
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.
- 571
45
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 ...
- 551
24
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);
...
- 56.4k
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 --...
- 371
17
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 ...
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 ...
- 16.3k
16
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 ...
- 182k
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. ...
- 241
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 ...
- 623
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 ...
- 131
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, ...
- 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 ...
- 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 ...
- 182k
11
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 ...
- 545
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 ...
- 201
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 ...
10
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)
);
...
- 184k
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
(
...
- 98.6k
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 ...
- 281
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
- 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 ...
- 271
8
votes
Accepted
Temporarily disable indexes before updating whole table
When you restore the data, use pg_restore --section=pre-data --section=data. This will leave off the indexes.
Then run the UPDATEs you want to, then run pg_restore --section=post-data
- 41.3k
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 ...
- 41.3k
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 ...
- 186
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 ...
- 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 ***************************
...
- 184k
7
votes
Accepted
On Amazon RDS, what consumes storage space beyond the database files?
AWS support came back with a comprehensive answer. In this case, it was transaction log backups, being written to the SQL disk before offloading to S3. There are a few more things which can consume ...
- 5,957
7
votes
Repacking RDS database with pg_repack isn't online
We found a similar issue. The issue appears to be that log tables created in the repack schema don't have the correct permissions. Our solution was to use ALTER DEFAULT PRIVALEGES to fix this:
CREATE ...
- 141
Only top scored, non community-wiki answers of a minimum length are eligible