Hot answers tagged

57

Try here for a basic intro to PostgreSQL and JSON. Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option. Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format {'id':1,'name':'David'} {'id':2,'name':'James'} ... I didn't have a running instance of ...


41

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


17

This is a dump file produced by pg_dump -Fc. To create a text file containing SQL commands from it, use pg_restore. The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).


15

If you're using psql then there is no reason to use \COPY at all. \t \a \o file.json SELECT row_to_json(r) FROM my_table AS r; This is the same method we use to get png/jpgs/tifs out of the database with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.


9

You can see a rough progress using the TOC list. First, get the TOC list of objects to be restored: pg_restore -l -f list.toc db.dump Then, you can see the TOC list line by line and compare the output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in. It is just a rough estimate though. First because each item from the ...


8

CREATE VIEW or CREATE TABLE... AS SELECT have to infer the column types for the relation to create. Sometimes the context is not sufficient to guess a datatype, for example when it's just a string literal. In this case it's created as unknown. Example : test=> CREATE VIEW testview AS SELECT 'bla' AS foobar; WARNING: column "foobar" has type "unknown" ...


8

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS. test=# CREATE TABLE table1 ( id serial PRIMARY KEY ); CREATE TABLE test=# CREATE TABLE table2 ( fkey int REFERENCES table1(id) ); CREATE TABLE test=# INSERT INTO table2 VALUES (1); ERROR: insert or ...


7

Learn and use an ETL tool You'll save yourself a lot of time and pain if you use ETL tools like Pentaho Kettle or Talend Studio to automate the data transfer and merging. They'll happily connect to the Pg instance and the Oracle instance and do the required copying and merging using quite flexible strategies. They can consume CSV dumps too. Use CSV dumps ...


6

For me @VĂ©race's answer didn't maintain the column names, but assigned default names (f1, f2, etc.) instead. I am using PostgreSQL 9.1 with the JSON extension. If you want to export the entire table, there is no need for a subquery. In addition, this will maintain the column names. I used the folowing query: COPY (SELECT row_to_json(t) FROM fred as t) to '/...


6

I will add a special caveat to Verace's answer. You need to do post processing on the outputted JSON file if you have text columns with backslash characters: \. Otherwise you will get duplicate (\ -> \\) at best and completely invalid JSON at worse ie: This: { "f1" : "crap\""}. Becomes { "f1" : "crap\\""}. Which looks fine but is completely invalid ...


6

Does it exist on Oracle? Similar utility exists in Oracle environment called Data Pump. How can it be done? Use expdp command line tool to perform full database export. Example: expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES NOLOGFILE=YES Then can use impdp to import it in another database. Example: impdp hr DUMPFILE=dpump_dir1:...


6

In SQL Server Management Studio (SSMS), you can generate a script that would create the structural components of your database (tables and views, as well as users, functions, and procedures). You could use the script(s) returned on your development DB. To run this, right-click on your database in SSMS, and choose Tasks => Generate scripts.... This opens a ...


5

The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug. And it's also testable when the sub-command could't be launched at all. Here's a basic skeleton in Unix shell that tests the ...


5

The temporary files are a side effect of the --format=tar option. A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.


5

What version are you running? I'm not able to reproduce your results on 11.2.0.4. Here's my schema: 16:36:09 system@DB> select owner, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'USER' group by owner; OWNER GB ------------------------------ ---------- USER 1156.41901 Here's the ...


4

The postgres database is just the default database that is created during initdb. It has no special meaning. If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it. I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from ...


4

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment: ProcessBuilder builder = new ProcessBuilder( "C:/postgres9/bin/pg_dump.exe", "-U", "myuser", "-h" , "localhost", "mydb"); ...


4

You should really increase max_locks_per_transaction. As specified in http://www.postgresql.org/docs/9.0/static/runtime-config-locks.html , changing this parameter may require to also change the System V shared memory. You have to increase that value also. In OS X this can be done as explained in http://www.postgresql.org/docs/9.0/static/kernel-resources....


4

Ok here's what I did: I was in the ORANT\BIN folder. I had to figure out what the SID was for the DB. Did that via an Oracle client which I tried to connect remotely to Oracle on the server. set oracle_sid=a exp73.exe System/manager file=myexp13xxxx.dmp full=y log=mylog130527.log


4

how to create "empty" or "blank" databases I would use pg_dump with the --schema-only option to create the dump for an empty database: pg_dump mydb -s > /mypath/myfile.sql Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster: createdb ...


4

Regarding the password, you have two choices: An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any computer ...


4

It sounds like you're trying to refresh a database that's a snapshot of another DB elsewhere, and do so without interrupting read access to the first DB. If so, the easiest way is actually to do it live in the snapshot DB. Just begin a transaction, truncate all the tables, and populate them with new data, then commit. Concurrent transactions will see the ...


4

You are essentially attempting to dump the entire oplog and hence paging in all of it, not just the most recent data. That is going to cause a lot of IO, especially when your oplog is too large to fit into memory. On a smaller database it's not a problem because the oplog is relatively small. The reason why it's dumping the full table is because I believe ...


4

Those "frozen" messages refer to pages that are known from the visibility map's freeze bits to contain only frozen tuples. A frozen tuple is one that all current and future transactions can "see", i.e. it committed before the start of any still-running xact. These shouldn't be removed, and are not the problem; they're the data that doesn't change. Vacuum is ...


4

You can use set timestamp <unix_epoch> which will make now() be fixed -- always return that value. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp Note that this will not affect sysdate(). To fix sysdate, you can start MySQL with the --sysdate-is-now option which will make sysdate() return the same value as ...


4

The size reported by psql -l includes the size of all indexes. The dump only contains the definition of the indexes, but no data for them, that's why the dump file is (substantially) smaller then the reported size on disk. In addition to the size of the indexes, the dump only contains the raw data, while the size on disk might include table bloat (free ...


3

"is there any possibility to use another way in converting oracle dump file?" Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication) Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse ...


3

Restoring a dump file, fundamentally, involves inserting all of the rows and building all of the indexes for all of the data. In that light, it's not particularly relevant how the dump file is generated, whether you use one or several files, or which of the available options you select (though some of them, like extended-insert could possibly make it worse ...


3

It could be most useful to start by using the pg_restore command to check the format of the file that's been sent. If your file was named "mydata.backup", the command would be: pg_restore --list mydata.backup (On Windows this would be pg_restore.exe) This will output some text to show the format of the file, and the data tables that are in the file. This ...


3

With the default settings, when rows (or entire tables) are deleted, SQLite does not remove empty pages from the database file. (This setting can be changed with PRAGMA auto_vacuum.) In most cases, keeping empty pages in the database file is good idea because they will be reused by new rows that are inserted later, and adjusting the database file to remove ...


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