I have a PostgreSQL version 10 and a PostGIS extension. In this old version of PostgreSQL, the PostGIS has also support for raster. But in the newer versions of PostGIS, the raster support in a separate extension called: postgis_raster. Thus, we wont have this line in our dumpall file:

CREATE EXTENSION postgis_raster;

And when I restore it it tells me it does not recognize the raster type! My file is very big if I do not zip it before storing it on the disk. If I zip it, I wont be able to change the lines in the file to add this extension to the dump file manually. I was thinking to do a pgdumpall with --global-only flag. Then, later dump each DB one by one using pgdump. However, I was afraid that I may miss a detail from my DB cluster. Is there a way to ask the pgdumpall to consider that postgis_raster is a separate extension and should be added to the dump file? How can I safely dump and restore my cluster?

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried [this answer](https://stackoverflow.com/a/2594564/3704831) , but using pg_dump in this way gives me over 800 lines of output for the 9 tables. For example, part of the output creating the first table is -- -- Name: popresearch_question; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE popresearch_question ( id integer NOT NULL, created_date timestamp with time zone NOT NULL, modified_date timestamp with time zone NOT NULL, question_text character varying(500) NOT NULL, question_template_id integer, question_type_id integer, user_id integer ); ALTER TABLE popresearch_question OWNER TO postgres; -- -- Name: popresearch_question_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE popresearch_question_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE popresearch_question_id_seq OWNER TO postgres; -- -- Name: popresearch_question_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres -- ALTER SEQUENCE popresearch_question_id_seq OWNED BY popresearch_question.id; and then later on are more ALTER statements: -- -- Name: popresearch_question id; Type: DEFAULT; Schema: public; Owner: postgres -- ALTER TABLE ONLY popresearch_question ALTER COLUMN id SET DEFAULT nextval('popresearch_question_id_seq'::regclass); and then later: -- -- Name: popresearch_question popresearch_question_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY popresearch_question ADD CONSTRAINT popresearch_question_pkey PRIMARY KEY (id); -- -- Name: popresearch_question popresearch_question_question_text_key; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY popresearch_question ADD CONSTRAINT popresearch_question_question_text_key UNIQUE (question_text); and after that there are at least a dozen more ALTER TABLE statements just for this one table scattered in the pg_dump output. Is there a way to get a simple, condensed CREATE TABLE statement that includes all the keys, constraints, etc.?

The documentation says this when using -t table: > Note When -t is specified, pg_dump makes no attempt to dump any other > database objects that the selected table(s) might depend upon. > Therefore, there is no guarantee that the results of a specific-table > dump can be successfully restored by themselves into a clean database. However, when the table has a sequence, and the owned by for the sequence is set to the table, it is included in the dump. Shouldn't it be excluded according to this part of the documentation? (If so, why are sequences exceptions?) What kind of database objects are going to be excluded in -t table mode then?

We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point. My database was 900MB, then I tried clearing it by deleting old history which is not necessary anymore and after that ran VACUUM FULL. The statistics in pgAdmin say that the database is now only 30MB. When I run the pg_dump command manually through a Command prompt, it creates a file of 22MB. When I run my daily task (windows task scheduler) it still creates a backup file of 1GB. What am I missing at this point?

I recently migrated a Postgres database from one server running Ubuntu 20.04 and Postgres 12 to another server running Ubuntu 24.04 and Postgres 16. To move the database from one server to the other, I did the following: ``` # On the Postgres 12 system $ sudo pg_dump -C sales_database > sales_database_bak.sql # On the Postgres 16 system $ sudo -u postgres psql 16 jump I performed, states > A dump/restore using pg_dumpall or use of pg_upgrade or logical replication is required for those wishing to migrate data from any previous release. See [Section 19.6](https://www.postgresql.org/docs/15/upgrading.html) for general information on migrating to new major releases. That is, the notes specifically cite pg_dumpall, not pg_dump, as necessary for the upgrade. The linked Section 19.6 continues to cite pg_dumpall without mentioning pg_dump. Of course, the two aren't that different: pg_dumpall calls pg_dump during operation. However, it's possible that pg_dumpall does something else that makes it different from pg_dump alone. I'm troubleshooting an error on the new system, and I want to make sure this isn't the issue. For the purposes of the upgrade, is pg_dump exactly equivalent to pg_dumpall for transferring a database in compliance with the upgrade requirements cited in the Postgres 15 upgrade notes?

I'm preparing a migration from one server to another. Actually, the same hardware but a full reinstall of a newer OS version. So everything including PostgreSQL needs to be reinstalled. But the user data must remain the same. This question is about restoring a full Postgres database server with all databases, structure, data, roles, passwords and everything. The old version is Postgres 13, the new is version 16. I can make a full dump with this command: pg_dumpall -U postgres -h localhost --clean --if-exists --quote-all-identifiers | gzip > ~/full-postgres.sql.gz On the new server, however, it can't be loaded. Currently this is in a test environment. Apparently I've created the default database with a new password, but the restoring now left the server in an unusable state. I did this: gunzip -c ~/full-postgres.sql.gz | psql -U postgres -h localhost It asked for the password and started running some commands. Then the last two lines are these:

\connect: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"
connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"

I cannot connect to the server anymore with the old or new password. Guess that attempt is lost and I'll have to restart my test with a fresh server installation. What's the correct procedure to get a totally complete dump of Postgres 13 and load all the data, passwords etc. into a fresh Postgres 16 installation? Running on Ubuntu 20.04/24.04. Needless to say that I cannot recreate users and passwords manually because I don't have all the passwords. Users must be able to connect with their existing password again on the new server. So whereever Postgres keeps the relevant data, it needs to be restored from the dump. While I'm thinking of it, a related configuration change is that the old server had pg_hba.conf entries with the "md5" method and the new will have "scram-sha-256" everywhere. Would this prevent preserving the passwords maybe? So would progress just leave me behind here? PS: I could also copy the raw database files on disk and restore those, if it helps to preserve all data. Just remember the version has changed from 13 to 16.

I have a bash script to backup MySQL databases. I have pasted the relevant part below (ignore the variables). The 2 lines I think I need to change to make this work in PostgreSQL are; DBS="$(mysql --login-path=dbbkup -Bse 'show databases')" and $MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE Can anyone advise as to how I can get the same functionality with PostgreSQL? # get all database listing DBS="$(mysql --login-path=dbbkup -Bse 'show databases')" # start to dump database one by one for db in $DBS do DUMP="yes"; if [ "$IGNOREDB" != "" ]; then for i in $IGNOREDB # Store all value of $IGNOREDB ON i do if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then DUMP="NO"; # SET value of DUMP to "no" #echo "$i database is being ignored!"; fi done fi if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database FILE="$BACKUPDIR/$NOW-$db.sql.gz"; echo "BACKING UP $db"; $MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE fi done

I'm trying to migrate a database from PostgreSQL to MySQL on an Ubuntu 23.04 system (latest version of both). So far, it looks like things are fine, except for one problem. In one of the character varying fields in PostgreSQL, there are \r\n characters. Yeah -- whoever developed this system made a front-end that allowed them. I'm saving the data using a command such as this:

-U mylogin --data-only --file=foo.sql --no-owner --column-inserts --table foo --quote-all-identifiers

which: - does not output the schema (I have separate CREATE TABLE statements for that) - creates only INSERT statements - does not associate an owner to the table --quote-all-identifiers was something I tried and it seems having it or not having it does not solve my problem. My problem is that I might have a row like: [1, "hello", "hello\r\nthere"] which looks like this in foo.sql: INSERT INTO TEST (id, comment1, comment2) VALUES (1, 'hello', 'hello there'); and MySQL does not like this (i.e. mysql -u mylogin -p . When doing backups and restorations within PostgreSQL, there wasn't any problem since pg_dump with default options uses COPY. I looked through the options for pg_dump and no obvious option jumps up at me that would solve this problem. I thought having a quote would (i.e., --quote-all-identifiers), but it did not. I'm not sure if I should look into how PostgreSQL is exporting it or how MySQL is importing it...or some other solution I haven't yet thought of. I was thinking of doing this on the PostgreSQL database:

TEST set comment2 = regexp_replace (description, E'([\\n\\r]+)', ' ', 'g');

(Untested) But I guess I would rather not change the source data if I can. Does anyone have a suggestion about what I should do? Or perhaps I'm going about this wrong... Thank you!