Hot answers tagged

3

Using PostgreSQL 12.4: $ pg_dump --help ... -O, --no-owner skip restoration of object ownership in plain-text format ... -x, --no-privileges do not dump privileges (grant/revoke) Here's an example: $ pg_dump -O -x mydb_development | gzip > mydb-2020-09-22.bak.gz


2

Very Simple. Drop Cascade public schema of your database and Create a new public schema in your database. You can use pgAdmin to do it.


2

Dump doesn’t account for dead tuples and only takes live tuples into account but dead tuples do account for space hence the space difference. The reason is dump being a logical one it will only create statements to insert your data and the dead rows would anyways be invisible to it. If you have lots of updates and deletes happening or in other words your db ...


2

Yes, dumping and restoring the database will have the same effect as VACUUM (FULL). But having to do either of these every day is painful. You have too little storage for your database. Artificially keeping it compact conserves space and speeds up queries, but it may slow down data modifications. The proper solution is to increase your storage and give your ...


2

Actually in these examples the used extensions don't really matter: both exports are plain text (i.e SQL) scripts. You decide which extension to use: if you export in plain SQL it's better to use .sql extension but you can choose another one. In general you decide which extension you use. Neither psql nor pg_restore have a mandatory extension for the export ...


2

If you want to restore individual databases, you should probably be using pg_dump, not pg_dumpall. And if you don't intend to restore over the top of an existing populated database, you shouldn't be using --clean. Backing up your entire cluster and migrating/cloning one database that is in it are different tasks, you probably want different commands to do ...


1

Unless somebody has overridden the application_name parameter for pg_dump, you could do this: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'pg_dump'; If that gives you 0, no pg_dump is running.


1

Run pg_waldump on the WAL segment. Then you will get lines like rmgr: Heap len (rec/tot): 115/ 115, tx: 564, lsn: 0/01FECDB0, prev 0/01FECC88, desc: HOT_UPDATE off 1 xmax 564 flags 0x60 ; new off 2 xmax 0, blkref #0: rel 1663/16385/1417 blk 0 In there you have tx: 564, lsn: 0/01FECDB0, which is your desired information.


1

Forcing a BOM and UTF-16 encoding for redirected output can be done somehow by Windows shells (personally I've experienced this with Powershell and Windows 10). See e.g. Resolve UTF-8, UTF-16, ASCII inconsistencies for some context. In the case of pg_dump, using the -f option to specifiy the output file instead of a shell redirection seems like a good idea ...


1

Ok, looks like I had to get rid of the pipe. Using the following pg_dump command worked for me: pg_dump -U username --format=c --file=filelocation.sqlc dbname


1

No, that is not possible. But there is no advantage in a custom format dump over a directory format dump (except that the latter is not a single file), so you might just use the directory format dump. You'd gain parallel pg_dump in the bargain.


1

MySQL answer because I don't know pg_dump. To allow SQL to continue with mysql during a mysql dump the following is required: Tables needs to be innodb. Check with show create table {tablename}, change with alter table {tablename} Engine=innodb if it isn't (will cause the table to be unavailable during the change). Use mysqldump with --single-transaction --...


1

I don't think that pg_dump or pg_dumpall or pg_restore can change the database name to be restored. However after restoring the database (with the same name) you can rename it with: alter database old rename to new;


1

The following commands export data without mixing it with role or privilege data so that you can import it to any role you specify. Also, you can make pg_dump compress the data to a tar file for you. pg_dump --format=t --blobs --verbose --no-privileges --no-owner \ --password --username mydbuser --database mydbname \ --file "mydbname_dump_$(date +%Y%m%...


1

I'm moving other databases (from the public schema) into a new schemas in a single database (thus a single connection can access them all). My approach was to load into the public schema then rename that. First, if you have anything in your public schema, rename to save. I found some extensions, "hstore" specifically, needed to be recreated in the ...


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