Hot answers tagged

7

Install it from PostgreSQL's own apt repository, adapted from these instructions. sudo apt-get install curl ca-certificates gnupg curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' sudo apt-get update ...


2

pg_dump -t will dump the definition of foreign key constraints. But will not dump the definition (or data) of the table which that constraint points to. Similarly, if you have a check constraint which uses a function, the definition of the function will not get dumped. I don't know the thought process behind dumping sequences. That does seem like a ...


2

That is silly You should instead take one pg_basebackup per day and archive the transaction log with archive_command. That way. you can use point-in-time-recovery to restore the database to any given point in time. See the documentation for more.


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

You don't actually have to dump to upgrade a PostgreSQL instance. For example, you can bring up a new cluster with access to an older cluster using the pg_upgrade command. If you're using XFS you can even use --clone. I would read the rest of the docs for pg_upgrade. If you're just looking to move the instance you can try pg_basebackup - though I'm not sure ...


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

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 ...


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

pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements. These statements will fail unless the initial connection to the database is made by a superuser (or the same user that owns all of the objects in the script). With -O, any user name can be used for the initial connection, and this user will own ...


1

Use the --no-owner option of pg_dump to skip the ALTER ... OWNER TO ... statements.


1

What the graph shows is pg_dump probably starting at 19:45 and having a normal memory consumption until 20:00. At 20:00, it appears that ~30 GB are suddenly allocated and not much is released until 23:15 when presumably the Out-Of-Memory condition occurs. From the comments, the kernel says: hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) ...


1

I've just tried this: pg_dump -s -t test.* -d test It nicely exported all table definitions, as expected. -s is the same as --schema-only.


1

I had also a similar case and I solved it via doing theese steps: First of all I made an initial psql_dump with the tables I wanted to dump: pg_dump -t my_table1 -t mytable2 > my_dump.sql Then I listed any sequence existing by filtering them, from the existing dump I made. In case of larger dumps use tools offered in bash or in powershell in order to ...


1

The pg_dump version is pg_dump (PostgreSQL) 9.3.23 Is that the program which was used to generate the "dirfmrt" directory? It doesn't matter what pg_dump you have install locally, if the backup file came from somewhere else. The version of the running postgresql server which was dumped, and the version of pg_dump used to dump it, are included in the dump. ...


1

The reason is that type casts do not live in a schema. If you specify -n public, you will only get objects from that schema, and that does not include the type cast.


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